none
automating with VBA coding RRS feed

  • Question

  • Hi

    I need help with some vba code in excel 2007. I tried to describe as detailed as possible, below. I can't see any options to attach a xl-doc here otherwise I could upload it, here it goes. Might be a hard nut to crack I guess.

    Many thanks for the help!!!

    Description of tabs and column-etiquettes:

    Tab Counterparties

    Counterpartyname1

    Counterpartyname2

    emailadress

    Contains a list of all counterparties!!!

    template

    Rows 1-16 to be used in each created tab!!!

    DATA

    AccountingDate
    Account

    CCY
    SUM_AmountOriginalCurrency
    sum_AmountLedgerCurrency
    CounterPartyEmittentID

    STEP 1

    I got a xl-file (above tabs!). One tab called counterparties. Column A, with header in cell A1 called "counterpartyname1"

    contains counterparty names. These names should be used to create new tabs named after the data in cell A2 and down to the last value.

    Column B has the header "counterpartyname2" in cell b1.

    It contains names to be used for lookup data in the tab "data" (see step3.) Names to lookup/match are in column F in the tab "DATA". 

    STEP2

    Next tab called "template" contains template-data in row 1-16. These rows should be inserted in every tab created (as step2 after creation of the tabs).

    Column C in counterparties tab contain emailadresses from c2 and down. These adresses should be inserted in the created tabs in cell M11. The email adress is on the same row as theCounterpartyname1used for naming the created tabs!

    STEP 3

    Next tab called "DATA" contains data in column A-F. Lookup of data is based on the column F named "CounterPartyEmittentID " in cell F1.  These values should be matched against values in tabcounterparties, column B.For each value f2 and down the vba code searchin tabcounterparties incolumn B and inserts the data found in tab DATA in the created tab created in step1. This data is placed on row 17 and down. A sum is then created based on the data inserted.  The sum should be calculated based on data in column B18. One sum for values that start with 81, one for 82, one 83 and one for 84. Sum is made of the values column  E.


    Sverreberre

    Saturday, June 2, 2012 5:34 PM

Answers

  • Step 1, 2 :

    'you will want several integer variables to do yours counts

    dim i, c, n, l as integer

    'c is going to be set to the number of counterparties you have. Adjust the range if you have more than 2000 listed.

    c = Sheets("Counterparties").Range("A2:A2000").C

    'from cell A (i set to 2) values - start creating the new tabs

    for each i = 2 to c

    'create them in order - so after

    sheets("template").Copy After:=Sheets(sheets.count)

    'set the name to the value in the cell

    ActiveSheet.Name = Sheets("Counterparties").Cells(i,1).value

    'set the email value to m11 in your new sheet

    ActiveSheet.Range("M11").Value = Sheets("Counterparties").Cells(i,3).value

    l=0

    set up a counter that in the new sheet, it will search through your data tab for itself

    for each n = 2 to 2000

    if Sheets("Counterparties").Cells(i,2).value = Sheets("Data").Cells(n,6).value

    'if it finds itself, insert the data from the data tab into the new worksheet.

    ActiveSheet.Cells(17+l,1).Value = Sheets("Data").Cells(n,1).value 

    ActiveSheet.Cells(17+l,2).Value = Sheets("Data").Cells(n,2).value

    ActiveSheet.Cells(17+l,3).Value = Sheets("Data").Cells(n,3).value

    ActiveSheet.Cells(17+l,4).Value = Sheets("Data").Cells(n,4).value

    ActiveSheet.Cells(17+l,5).Value = Sheets("Data").Cells(n,5).value

    ActiveSheet.Cells(17+l,6).Value = Sheets("Data").Cells(n,6).value

    end if

    next n

    next i

    end sub

    Step 3- Summing

    I would set up your sums at the top of the page, in the template file. Set up the formulas the way you want them so that they calculate based on the data inputted into the fields. Several reasons: in the template, freeze the frame where your headers are, this lets you scroll no matter how long the list gets. This should parse all of the "data" tab into the separate tabs you want. You may have to debug a little, but this at least gets you the loops you need.

    • Marked as answer by sverreberre Friday, June 15, 2012 1:06 PM
    Thursday, June 14, 2012 1:14 PM

All replies

  • Step 1, 2 :

    'you will want several integer variables to do yours counts

    dim i, c, n, l as integer

    'c is going to be set to the number of counterparties you have. Adjust the range if you have more than 2000 listed.

    c = Sheets("Counterparties").Range("A2:A2000").C

    'from cell A (i set to 2) values - start creating the new tabs

    for each i = 2 to c

    'create them in order - so after

    sheets("template").Copy After:=Sheets(sheets.count)

    'set the name to the value in the cell

    ActiveSheet.Name = Sheets("Counterparties").Cells(i,1).value

    'set the email value to m11 in your new sheet

    ActiveSheet.Range("M11").Value = Sheets("Counterparties").Cells(i,3).value

    l=0

    set up a counter that in the new sheet, it will search through your data tab for itself

    for each n = 2 to 2000

    if Sheets("Counterparties").Cells(i,2).value = Sheets("Data").Cells(n,6).value

    'if it finds itself, insert the data from the data tab into the new worksheet.

    ActiveSheet.Cells(17+l,1).Value = Sheets("Data").Cells(n,1).value 

    ActiveSheet.Cells(17+l,2).Value = Sheets("Data").Cells(n,2).value

    ActiveSheet.Cells(17+l,3).Value = Sheets("Data").Cells(n,3).value

    ActiveSheet.Cells(17+l,4).Value = Sheets("Data").Cells(n,4).value

    ActiveSheet.Cells(17+l,5).Value = Sheets("Data").Cells(n,5).value

    ActiveSheet.Cells(17+l,6).Value = Sheets("Data").Cells(n,6).value

    end if

    next n

    next i

    end sub

    Step 3- Summing

    I would set up your sums at the top of the page, in the template file. Set up the formulas the way you want them so that they calculate based on the data inputted into the fields. Several reasons: in the template, freeze the frame where your headers are, this lets you scroll no matter how long the list gets. This should parse all of the "data" tab into the separate tabs you want. You may have to debug a little, but this at least gets you the loops you need.

    • Marked as answer by sverreberre Friday, June 15, 2012 1:06 PM
    Thursday, June 14, 2012 1:14 PM
  • Thanks, very nice to have the description which teachies a vba-dummy like me new things.

    Best Sverre


    Sverreberre

    Friday, June 15, 2012 1:06 PM