Copying data in daily monthly files into one consolidated template or file.

[iP_123]

Hi Team.

Perhaps this silly question has been asked and resolved once before but I could not find it anywhere but nevertheless I will appreciate your help as always. I am trying to figure out an efficient way to write a vba to copy all data from within daily files in a folder (the daily files will begin with 01 for January and 02 for February and etcetera) into a new template file. The daily files reside in two seperate folders but the vba copies all files with 01 in the file name into the template.
The goal is to create monthly consolidated data out of the daily files. I will essentially run this vba once a month to consolidate all the data in the daily files into one template. So next month, I will run the vba for all files in both folders that has 10 in the file name for October.

Any help will greatly be appreciated.

 

[alansidman]

I would do this with Power Query. Get Data from a Folder. Use a parameter to select the particular month or filter it directly in PQ.

 

[iP_123]

Thank you alansidman and very much appreciated however I am encountering errors so I did some more research and I was able to find the below vba which I tried repurposing. The help I need to make this work is not to specify a range in the vba as the number of row data varies from file to file.
Is there a way to set the range object to be all the data in columns a through Y in each file and not just 300 as seen in the code below?

Sub Copy_Range_From_Workbooks()

Dim fileCells As Range, fileCell As Range
Dim destCells As Range, r As Long
Dim fromFile As String, fromWorkbook As Workbook

With ActiveWorkbook
With .Worksheets("Sheet1")
Set fileCells = .Range(.Range("A2"), .Cells(.Rows.Count, "A").End(xlUp))
End With
Set destCells = .Worksheets("Sheet2").Range("A1:Y300")
End With

Application.ScreenUpdating = False

r = 0
For Each fileCell In fileCells
fromFile = fileCell.Value
If Right(fromFile, 1) <> "\" Then fromFile = fromFile & "\"
fromFile = fromFile & fileCell.Offset(, 1).Value
Set fromWorkbook = Workbooks.Open(fromFile)
destCells.Offset(r).Value = fromWorkbook.Worksheets("2022 - BMW").Range("A1:Y300").Value
fromWorkbook.Close SaveChanges:=False
r = r + 300
DoEvents
Next

Application.ScreenUpdating = True

MsgBox "Finished"

End Sub

 

[alansidman]

I don't work in VBA for this type of scenario. You will need to wait for a VBA proficient individual. Just so you know, PQ will be faster than any VBA code you finally end up with.

 

[iP_123]

Thank you kindly alansidman. I look forward to the vba assistance per my request to compare.

 

[iP_123]

Good evening

Any advice or guidance on my query I will truly appreciate.

Regards,
ip