Excel macro, copy and paste data in next available row of a changing referenced column

Hi;

I am trying to run an excel macro but I have not been able to make it work correctly. Where I have gotten this far with the VBA code is the following:

Please Login or Register  to view this content.

The problem is that Range("M1048576") and Cells(NextRow, 13) indicate fixed column "M" in "Worksheet2", while I need this column to be variable depending on the value of "W4" in "Worksheet1".

What I need the macro to do is the following:

- I have data in "worksheet1" in cells "T4", "U4" and "V4". The data in these cells vary because it is referenced to some input in other cells. Each cell (T4, U4 and V4) indicate a date, a description and an amount respectively.

- There is another value in cell W4 ("woksheet1") which indicates a number (1 - as many as needed). This number indicates the heading of the column in a table in "worksheet2" in which the values of "T4", "U4" and "V4" (of "worksheet1") should be pasted. Please note that column 1 (of my table in "worksheet2") does not match column "A" of "worksheet2", column 1 (of my table) starts in column "Q" of "worksheet2".

- The trick is that each time I run the macro, these three values should be copied and pasted in the next available row, starting in column (#) and going to the right ("worksheet2"). The values of "W4" have a difference of + and - 3 so that they respect the 3 cells needed to paste the date, the description and the amount of each operation.

Example: (what the macro should do)
- "Worksheet1": "T4"= 2 feb 2013, "U4"= "credit payment", "V4"= $100, "W4" = 37
- Macro should Copy "T4", "U4" and "V4" of "Worksheet1", identify the value of "W4" (36) in "Worksheet1" and find the correspondent column with heading (36) in "Worksheet2".
- Macro should paste the data in the next available row starting in column 36 and going to the cells to the right. (which would be column 36, 37 and 38 in this example) of my table in "Worksheet2".
- This should work for any input in "T4", "U4" and "V4", identifying where to copy the data in "Worksheet2" depending on the value of "W4".

Hope someone can help me, I have been breaking my mind for some days now without any success.

Thanks!


Administrative Note:

• Please see Forum Rule #3 about the proper use of code tags and adjust accordingly...
• Added for you this time, but please use them in the future…Thanks.