Copy data from one sheet to another sheet but in a blank column

Question

I've got a sheet (let's call it sheet1) that gets updated on open. So let's say in sheet1 I have:

a1  one
a2  two
a3  three

Sheet1 is already configured to update data on open.

I want that data to then populate into Sheet2 in the next available blank row. So, if I already have data in a1-a3, I want this new data to copy into b1-b3 and so on.

Any thoughts on how I can make this happen? Thanks! Ken

Answer 1

You should submit code for questions and not ask for code for questions, but this is a pretty simple one. If this needs to be tweaked to fit your needs, then try to tweak it. If you get stuck, then ask a new, more specific question about how to do it, or why it's erroring.

Anyway, you'll need some VBA:

Sub someVBA()

    'Set the sheets we are working with - change as needed
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Worksheets("Sheet1")
    Set s2 = Worksheets("Sheet2")

    'Find the last column in s2 by going way out to the end of
    ' of the sheet and using end(xlToLeft) and adding 1 more column
    ' to that. That's the next blank column
    Dim lastCol As Integer
    lastCol = s2.Cells(1, 1000).End(xlToLeft).Column() + 1

    'Copy it over
    s2.Columns(lastCol).Value = s1.Columns(1).Value

End Sub