DangerSloth
New Member
- Joined
- Sep 11, 2021
- Messages
- 3
- Office Version
- 2013
- Platform
- Windows
Hi!
I am fairly new to VBA so I am having trouble in creating a code.
I did found one here but I don't think it is specifically tailored to what I need.
In simple words, I need a code to copy the data from all the files in the folder to my master file. All files only have 1 sheet of them and the number of data will vary per file. So I will need to use a code that will find the last column and row that has data and copy it to the master file. Then the next file that will be copied will start in the next row where the last data from the previous file stopped pasting (i hope this makes sense).
Also, the master file has a header on Row 1 and should not over written. The source files also has headers on row 1 which should not be copied, so the code should start copying from "A2" to the last column and row. of the source files.
I found this code here courtesy of a Mr. Mumps bat that was years ago and might not work now and this might not be tailored to what I need.
I hope someone can help me.
I am fairly new to VBA so I am having trouble in creating a code.
I did found one here but I don't think it is specifically tailored to what I need.
In simple words, I need a code to copy the data from all the files in the folder to my master file. All files only have 1 sheet of them and the number of data will vary per file. So I will need to use a code that will find the last column and row that has data and copy it to the master file. Then the next file that will be copied will start in the next row where the last data from the previous file stopped pasting (i hope this makes sense).
Also, the master file has a header on Row 1 and should not over written. The source files also has headers on row 1 which should not be copied, so the code should start copying from "A2" to the last column and row. of the source files.
I found this code here courtesy of a Mr. Mumps bat that was years ago and might not work now and this might not be tailored to what I need.
I hope someone can help me.
VBA Code:
Sub CopyRange()
Application.ScreenUpdating = False
Dim wkbDest As Workbook
Dim wkbSource As Workbook
Set wkbDest = ThisWorkbook
Dim LastRow As Long
Const strPath As String = "Z:\DoX\CopyFiles Test\Source Files"
ChDir strPath
Dim strExtension As String
strExtension = Dir("*.xlsx*")
Do While strExtension <> ""
Set wkbSource = Workbooks.Open(strPath & strExtension)
With wkbSource
LastRow = .Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
.Sheets("Sheet1").Range("A2:O" & LastRow).Copy wkbDest.Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
.Close savechanges:=False
End With
strExtension = Dir
Loop
Application.ScreenUpdating = True
End Sub