Excel VBA Pasting to Columns Based on The Date

[corycrum]

I produce a daily report and need to create a macro that reads the date on the report, and copies a certain column of data to another column depending on the date of the report. For example, if the date in cell A1 is 3/7/2007, then I need the macro to copy the contents in column B, and paste them into the "March" column. When the date advances to the new month, say 4/1/2007, I need the macro to begin pasting the data into the "April" column instead of the "March" column.

This will be my first macro, so please take it easy in the explanation.

Thanks so much,

Cory

 

[royalcheese]

look into using the macro recorder , this can give you the copy and paste , code - coloring etc.

secondly look at making 2 while loops, to loop firstly through your data headers , then loop through the data to match header e.g. (code typed not checked but along these lines)

[/code]
'loops through data
while worksheets("data").cells(x,1).value <> ""

'loop to find your date
while worksheets("Results").cells(y,1).value <> worksheets("data").cells(x,1).value
'increment
y = y +1
until

' if they match then copy
if worksheets("Results").cells(y,1).value = worksheets("data").cells(x,1).value then
worksheets("Results").cells(y,2).value = + worksheets("Results").cells(y,2).value worksheets("data").cells(x,1).value
endif
x = x +1
loop

[\code]

 

[SkipVought]

Cory,

What you describe is the kind if thing that Excel already has as a built-in feature. It does not require VBA Code.

You need to get familiar with the Data Analysis & Rerpoting features of Excel. The PivotTable Wizard can help you set up a PivotTable that can generate a report by month, as you have described, without all that copy and paste stuff.

A report like this can be generated in a matter of SECONDS!

Skip,
[sub]
[red][/red]
[/sub]

 

[corycrum]

Thank you both very much for your prompt reply and help.

I have not yet had an opportunity to try and integrate the code with the copy and paste macro that is currently in the file but will keep you posted as soon as I have a chance to test it out.

Cory