Excel, cycle through columns and rows

Question

I have an excel table that calculates how many materials we need to order per job we are doing.

What I want to accomplish is to create a button that will copy the existing table on to page 2 of my excel workbook. The table is two columns wide. Therefore I would like to click the button, it copies the table thats on A and B of page 1 onto page 2 A and B. After having done so, I want excel to remember A and B are taken and next time I click the button, it goes in column C and D, then E and F and so on (always on page 2).

to explain why I would like this, I need to order 1000 sq ft of materials, but in sections. only 250 sq ft of materials at a time. So, I want to fill out how much im ordering, click the button and it saves the information of what was ordered on page 2. That way its easy to go back and see what was done, what date, how much was ordered, what is left to order, etc.

I know with numbers it would be easy by just incrementing i by two at every button click, but I dont know how to go about doing this for the excel columns (letters).

Is there some easy command I just cannot find online to do this sort of thing ?

Thanks for your help !

Answer 1

What you should learn in VBA and modify in this code below is how to determine the actual range of the table on Sheet1 -- instead of hard-coding it to "A1:B10"

UPDATE Not sure why the previous version didn't work for you, but I've updated the code below to store the next column location in a helper cell to see if that works for you.

Option Explicit

Sub Button1_Click()
    Dim srcTable As Range
    Dim dstTable As Range
    Dim nextLoc As Range
    Set srcTable = Sheets("Sheet1").Range("A1:B10")
    Set nextLoc = Sheets("Sheet2").Range("A20")

    '--- if this is your first copy, then cell A1 should be empty
    If IsEmpty(Sheets("Sheet2").Range("A1").Value) Then
        Set dstTable = Sheets("Sheet2").Range("A1:B10")
        nextLoc.Value = 3                           'next location is column 3
    Else
        '--- we're adding the next table...
        Set dstTable = Sheets("Sheet2").Cells(1, nextLoc.Value)
        nextLoc.Value = nextLoc.Value + 2
    End If
    srcTable.Copy dstTable
End Sub