Macro to copy and paste repetitive data.

[berberda1]

I am in need of a macro to help me with a very repetitive task. Each month I prepare a monthly summary in which 2013 data is compared 2012 data. Using a summary file provided to me, it lists monthly data and a yearly total for different clients (13 rows x 3 columns for each client). The issue I am having is that I have to manually enter in the 2012 data to compare to the 2013 data. Below is an example of the format the the summary produces (this example range is considered the 2013 amount).

​

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Month[/TD]
[TD="align: center"]Amount A[/TD]
[TD="align: center"]Amount B[/TD]
[TD="align: center"]Amount C[/TD]
[TD="align: center"]Amount D[/TD]
[/TR]
[TR]
[TD]Jan
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aug[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sept[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oct[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nov[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dec[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

For the sake of example, say these are columns A-E. I am in need of code that will copy the 2012 data from another Sheet and paste it into columns H-J. I thought about using a Vlookup by client name but the problem is for each row, there is the same client name. So a vlookup would return the values listed for Jan. If there is a way to treat a specific range of cells as a group. That way I could pull in the 2012 "group" or 2012 data relative to each client.
I am no expert at writing code so any suggestions would be helpful. This is a very repetitive task that I spend too much time on each month. A macro to help to save some time as well as cut down on human error.

 

[agmc925]

Hello Berberda1

Their are some really smart programmers on this board, but I'm not one of them. They know how to use advanced functions like index and variable arrays, which is probably what you need. If you clarify your request with the data layout from your source and destination sheets, I'm sure someone would take care of it.

The reason I responded though is:

If your source and destination sheets have the same layout, couldn't you go to the top left cell of each customers area of the destination file, press = to start a formula, navigate to the top left corner of the corresponding customers area of the source sheet, then press enter, which will take you back to the destination sheet. The cell you started in would now contain a formula something like (=Source Sheet!E2). From there you could fill right 2 columns, than fill down 12 rows.

It's not as automatic as you would like it but it sure beats manual entry.

All the best