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