Vlookup into next blank column

[Janspook03]

Hi everyone,
I'm an excel newbie and trying to figure out how to make something work in VBA. Y'all seem like a really nice bunch, so I'm hoping you can help me out!

I've managed to automatically create a pivot table containing days worked per week per person from a raw data file and would like to create a macro that will automatically show the values of that pivot table into an overview. So I can collect the data for every month in one sheet.
To achieve this, I figured I could use a VLookUp code that transfers the data from my pivot table to the overview adding 4 to 5 weeks every month.

I got this far from another guide I found online:

Sub VLOOKUPAnotherSheet()
    
    For Each cell In ThisWorkbook.Sheets("Pastetest").Range("A2:A2000")
    cell.Offset(0, 1) = Application.VLookup(cell, Sheets("Pivot Sheet").Range("A3:B2000"), 2, False)
    Next

End Sub

But I can't figure out how to change the code to add the vlookup to the next blank column.
(every time I try to add an .End(x1Up) or other things I've seen in other posts, it just doesn't work.)

If anyone can help me to make sense of it all, it would be greatly appreciated!

 

[MARK858]

The End(x1Up) you have seen in other posts should be End(xlUp) i e. a lower case L and that would be looking for the last row with data not a column

 

[Janspook03]

The End(x1Up) you have seen in other posts should be End(xlUp) i e. a lower case L and that would be looking for the last row with data not a column

Haha that certainly clears things up! Thanks for that.

Though if I try the following I get a runtime error. So I'm still not sure how to make it scoot over. Any ideas?

Sub VLOOKUPAnotherSheet()
    
    For Each cell In ThisWorkbook.Sheets("Pastetest").Range("A2:A" & Sheets("Pastetest").Range("A" & Column.Count).End(xlToLeft).Row)
    cell.Offset(0, 1) = Application.VLookup(cell, Sheets("Pivot Sheet").Range("A3:B2000"), 2, False)
    Next

End Sub

 

[MARK858]

ThisWorkbook.Sheets("Pastetest").Range("A2:A" & Sheets("Pastetest").Range("A" & Column.Count).End(xlToLeft).Row)

Can you explain in words what you are trying to do with the line above as the bit in red doesn't make sense with the rest of the line

 

[Vogateer]

In this case, you're trying to find the last row in Column A, which should look like this:

Sub VLOOKUPAnotherSheet()
    Dim cell As Range
    
    For Each cell In ThisWorkbook.Sheets("Pastetest").Range("A2:A" & Sheets("Pastetest").Range("A" & Rows.Count).End(xlUp).row)
        cell.Offset(0, 1) = Application.VLookup(cell, Sheets("Pivot Sheet").Range("A3:B2000"), 2, False)
    Next

End Sub