Macro - find last used col, add one, and paste data

[macfam929]

I am writing a macro to find the last used column in a worksheet, add one to it, select row 1 in that column, and then paste a bunch of data from another worksheet into the empty column. However it wont work. It successfully finds the last column but will not paste into it.

I have the following code:

Dim LastCol As Integer
Sheets(LastSheetName).Select
Range("T1:T69").Select
Selection.Copy
Sheets("Summary").Select
LastCol = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
Range(Cells(1, LastCol + 1)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False

any help would be greatly appreicated! Thank you.

FYI: If I replace "Range(Cells(1, LastCol + 1)).Select" with a hardcoded value like "Range("F1").Select", it works no problem.

 

[njimack]

You don't need Range in there, since you're only pasting to a single cell. Also, you don't need to select sheets or cells to perform actions on them. Something like this:

Sub a()
Dim LastCol As Integer
Dim LastSheetName As String
Sheets(LastSheetName).Range("T1:T69").Copy
With Sheets("Summary")
    LastCol = .Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByColumns).Column
    .Cells(1, LastCol + 1).PasteSpecial xlValues
End With

End Sub

 

[VoG]

Does this work?

Dim LastCol As Integer
Sheets(LastSheetName).Range("T1:T69").Copy
With Sheets("Summary")
    LastCol = .Cells.Find(What:="*", searchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
    .Cells(1, LastCol + 1).PasteSpecial Paste:=xlPasteValues
End With

 

[macfam929]

That works great! Thank you so much!!