Paste into successive columns instead of rows

[armywalrus]

Hello. I am currently using the following script to grab data from other workbooks and paste it into a master sheet. It works well, but it takes the data and pastes it into successive rows. I need the copied data pasted into successive columns instead, and I can't figure out which part to edit. I changed the offset but it does not paste in successive columns, it overwrites what is already there. Any help would be appreciated, thank you!!
Sub Copy_Paste_Ext_Test()
'loop through files in a folder, copy and paste data into new Excel workbook


' Declare variables
Dim folderPath As String
Dim Filename As String
Dim wkbDest As Workbook
Dim wkbSource As Workbook
Set wkbDest = ThisWorkbook
Dim LastRow As Long

' Optimize macro speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

' Set variable for folder path
folderPath = "D:\BBC\GIT_Repos\but-ind-data-pt-06-2020-u-c\Unit_02_VBA_Scripting\LoopTest\filestoopen\"

If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"

' Set variable equal to the name of any excel file
Filename = Dir(folderPath & "*.xls*")

' Do While loop: for each workbook in specified folder, while filename is not blank, opens and copies data, pastes into Master, then closes each file
Do While Filename <> ""
Set wkbSource = Workbooks.Open(folderPath & Filename)
With wkbSource
LastRow = .Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
.Sheets("Sheet1").Range("A1:C4").Copy wkbDest.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
.Close savechanges:=False
End With
Filename = Dir
Loop

ResetSettings:
'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub

 

[Joe4]

Welcome to the Board!

Assuming that you are always pasting to row 1, try changing this line:

Sheets("Sheet1").Range("A1:C4").Copy wkbDest.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

to this:

Sheets("Sheet1").Range("A1:C4").Copy wkbDest.Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)

 

[armywalrus]

Welcome to the Board!

Assuming that you are always pasting to row 1, try changing this line:

Sheets("Sheet1").Range("A1:C4").Copy wkbDest.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

to this:

Sheets("Sheet1").Range("A1:C4").Copy wkbDest.Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)

That worked perfectly!! Thank you so much!!

 

[Joe4]

You are welcome!

I hope it maks sense when you break that line down. Basically, you are finding the last populated cell in row 1, and then moving over one more column to the right.
If you have any questions about that line of code, please do not hesitate to ask.

 

[armywalrus]

You are welcome!

I hope it maks sense when you break that line down. Basically, you are finding the last populated cell in row 1, and then moving over one more column to the right.
If you have any questions about that line of code, please do not hesitate to ask.

It makes sense now that I am looking at it, if THAT makes sense. Thank you again!