Macro

[bettbacker03]

I am having difficulties getting my macro to work properly. I have
sheet of data that I am updating with NEW data each month (overwrittin
the previous months data) and I want that data to be automatically pu
into sheets that correspond to the a account number that is assigned i
Sheet 1. I have started a macro, but when I try and run it with ne
data, the old data is overwritten. I'm not sure how to make it go t
the next active cell in that column. Below is my script:

Sub Test2()
'
' Test2 Macro
'

'
Range("B2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Data!C[-1]:C[5],2,FALSE)"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Data!C[-2]:C[4],3,FALSE)"
Range("C3").Select
ActiveCell.FormulaR1C1 = ""
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Data!C[-3]:C[3],4,FALSE)"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],Data!C[-4]:C[3],5,FALSE)"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Data!C[-5]:C[1],6,FALSE)"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-6],Data!C[-6]:C,7,FALSE)"
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Range("A3").Select
End Sub

Can anyone help? Also, I'm interested in how I make this macro appl
for all sheets within my workbook. Thank you

 

[GS]

Uh.., I'm going to urge you strongly to change your approach! Instead
of inserting formulas that pull data from Sheets("Data") only to then
convert the data to values, why not just directly assign the target
cell the value? Also, you don't need to select anything to do this!

Explain the layout of the data on Sheets("Data") and where each value
should be placed on each of the other sheets. Include the account
number:sheetname list and where it resides (range.address) on
Sheets("Sheet1").

It appears you are trying to consolidate account data to individual
sheets for each account. A single sheet listing all accounts with
columns/sections for each month makes much more sense to me. This
would/could also serve as a summary sheet showing totals for month,
qtr, and YTD if desired.

If you insist on sticking with separate sheets for each account then it
would serve you much better to put the account number in a named cell
defined with local scope so it can be reused on every sheet for code
reference...

To define a range named "AcctNum" on every sheet:

Select the cell that has the account number in it;
Click in the NameBox to the left of the FormulaBar;
Enter the name as follows...

'<sheetname>'!AcctNum

Hit 'Enter'.

...where you would substitute the actual sheetname for <sheetname>. For
example, for a sheet named "Account Name" you would type...

'account name'!AcctNum

...into the NameBox. Now your code can ref that cell by name and pull
the respective data from Sheets("Data"). This will obviate the need for
an account#/sheetname list because the account# is defined on each
sheet that will pull data from the monthly data sheet.

It would also be good if the areas to receive data are identical on
each sheet. This, of course, would happen by attrition if all
account/months were consolidated on a single worksheet.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[bettbacker03]

Thank you for your reply Garry. My intensions are to be able to "dump
in information on a monthly basis into a "Data" tab and I want it t
automatically go to the appropriate companies data tab based on th
account number. I will have a graph going for each account trackin
different information. I know how to make a Dynamic Table and Dynami
Graph in order to allow things to update automatically there, but I'
having troubles identifying a good way to be able to just dump in th
fields on the "Data" tab and have everything else automatically update.
Let me know if you can help me out! Thanks!

;1610503']Uh.., I'm going to urge you strongly to change your approach
Instead
of inserting formulas that pull data from Sheets("Data") only to then
convert the data to values, why not just directly assign the target
cell the value? Also, you don't need to select anything to do this!

Explain the layout of the data on Sheets("Data") and where each value
should be placed on each of the other sheets. Include the account
number:sheetname list and where it resides (range.address) on
Sheets("Sheet1").

It appears you are trying to consolidate account data to individual
sheets for each account. A single sheet listing all accounts with
columns/sections for each month makes much more sense to me. This
would/could also serve as a summary sheet showing totals for month,
qtr, and YTD if desired.

If you insist on sticking with separate sheets for each account then i

would serve you much better to put the account number in a named cell
defined with local scope so it can be reused on every sheet for code
reference...

To define a range named "AcctNum" on every sheet:

Select the cell that has the account number in it;
Click in the NameBox to the left of the FormulaBar;
Enter the name as follows...

'<sheetname>'!AcctNum

Hit 'Enter'.

...where you would substitute the actual sheetname for <sheetname>. Fo

example, for a sheet named "Account Name" you would type...

'account name'!AcctNum

...into the NameBox. Now your code can ref that cell by name and pull
the respective data from Sheets("Data"). This will obviate the need fo

an account#/sheetname list because the account# is defined on each
sheet that will pull data from the monthly data sheet.

It would also be good if the areas to receive data are identical on
each sheet. This, of course, would happen by attrition if all
account/months were consolidated on a single worksheet.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussio

 

[GS]

Thank you for your reply Garry. My intensions are to be able to

"dump"
in information on a monthly basis into a "Data" tab and I want it to
automatically go to the appropriate companies data tab based on the
account number. I will have a graph going for each account tracking
different information. I know how to make a Dynamic Table and
Dynamic
Graph in order to allow things to update automatically there, but I'm
having troubles identifying a good way to be able to just dump in the
fields on the "Data" tab and have everything else automatically
update.
Let me know if you can help me out! Thanks!

I conclude, then, that these 'account numbers' are actually 'client
accounts' as opposed to 'Chart of Accounts' items. In this case
separate sheets makes sense!

If you do what I suggested for adding the named range (as exampled) to
each sheet then the task will be much easier to code. Optionally, you
could upload your file to a website and post a link to it. Make sure it
has account sheets and a sample of the 'Data' fields you want parsed to
each account sheet. (Use dummy account numbers and data)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion