Increment column reference in formula

Question

I have data which I have to enter into a table that has the following layout:

I need to store it in another worksheet that has this layout:

I am trying to create a macro that will accomplish 3 things:

1. Copy the contents in the first table to a column in another worksheet

2. clear the original table data (but not the headers q, w, e, etc)

3. Increment the column reference so that the next time the macro is ran, it will copy the data into column C in the second table, and the third time into column D, and so on.

This is what I have tried:

Line 1: Sheets("sheet2").Range("B1").Value = Sheets("sheet1").Range("A5").Value

Line 2: Sheets("sheet1").Range("A5:D6").ClearContents

In order to accomplish 3), I have to manually change "A5" to "B5" in the code, and to do so for each cell (of which there are about 60). Is there a way to automate this?

Answer 1

You can use excel's offset() function.

To change A5 to B5 you can do

For x = 1 to N
Sheets("sheet2").Range("B1").Value = Sheets("sheet1").Range("A5").offset(0,x).Value

If you want to do it only once then you will not need the for loop.