Paste into successive columns instead of rows

armywalrus

New Member
Joined
Jan 25, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Assuming that you are always pasting to row 1, try changing this line:
VBA Code:
Sheets("Sheet1").Range("A1:C4").Copy wkbDest.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
to this:
VBA Code:
Sheets("Sheet1").Range("A1:C4").Copy wkbDest.Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
 
Solution

armywalrus

New Member
Joined
Jan 25, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Assuming that you are always pasting to row 1, try changing this line:
VBA Code:
Sheets("Sheet1").Range("A1:C4").Copy wkbDest.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
to this:
VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 25, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,321
Messages
5,623,994
Members
416,004
Latest member
reitz1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top