Copy worksheet from multiple workbooks and append to one worksheet

Pumper

Board Regular
Joined
Sep 12, 2013
Messages
78
Office Version
  1. 2016
Hi All,

Have watched multiple videos and looked through examples but can't seem to find what I am after...

I would like to look through a folder and if the file contains todays date anywhere in the name of the file then copy to sheet 1 of the current workbook (not in the same directory). These source workbooks only ever contain one worksheet but the name changes constantly.

If there are multiple files containing todays date I would like them to append to the same worksheet 1.

Sample folder where source files are G:\Home\Performance
Directory of workbook where the paste/append takes place C:\Users\me
Sample file name example HPhde_20220321_da.csv (date will always start and end with an underscore and will always be .csv).

Hopefully that is clear...

Any help would be much appreciated.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,478
Try this macro.
VBA Code:
Public Sub Copy_Sheet_From_CSV_Files()

    Dim matchFiles As String
    Dim folderPath As String, fileName As String
    Dim copyFromWorkbook As Workbook
    Dim copyToRange As Range

    matchFiles = "G:\Home\Performance\*_????????_*.csv"
    
    With ActiveWorkbook.Worksheets(1)
        Set copyToRange = .Cells(.UsedRange.Row, "A")
        If copyToRange.Row > 1 Then Set copyToRange = copyToRange.Offset(1)
    End With
    
    Application.ScreenUpdating = False
    
    folderPath = Left(matchFiles, InStrRev(matchFiles, "\"))
    fileName = Dir(matchFiles)
    While fileName <> vbNullString
        If InStr(1, fileName, "_" & Format(Date, "YYYYMMDD") & "_") Then
            Set copyFromWorkbook = Workbooks.Open(folderPath & fileName)
            copyFromWorkbook.Worksheets(1).UsedRange.Copy copyToRange
            copyFromWorkbook.Close SaveChanges:=False
            Set copyToRange = copyToRange.Worksheet.Cells(copyToRange.Worksheet.UsedRange.Rows.Count, "A").Offset(1)
            DoEvents
        End If
        fileName = Dir
    Wend
        
    Application.ScreenUpdating = True
    
    MsgBox "Done"
    
End Sub
 
Solution

Pumper

Board Regular
Joined
Sep 12, 2013
Messages
78
Office Version
  1. 2016
Try this macro.
VBA Code:
Public Sub Copy_Sheet_From_CSV_Files()

    Dim matchFiles As String
    Dim folderPath As String, fileName As String
    Dim copyFromWorkbook As Workbook
    Dim copyToRange As Range

    matchFiles = "G:\Home\Performance\*_????????_*.csv"
   
    With ActiveWorkbook.Worksheets(1)
        Set copyToRange = .Cells(.UsedRange.Row, "A")
        If copyToRange.Row > 1 Then Set copyToRange = copyToRange.Offset(1)
    End With
   
    Application.ScreenUpdating = False
   
    folderPath = Left(matchFiles, InStrRev(matchFiles, "\"))
    fileName = Dir(matchFiles)
    While fileName <> vbNullString
        If InStr(1, fileName, "_" & Format(Date, "YYYYMMDD") & "_") Then
            Set copyFromWorkbook = Workbooks.Open(folderPath & fileName)
            copyFromWorkbook.Worksheets(1).UsedRange.Copy copyToRange
            copyFromWorkbook.Close SaveChanges:=False
            Set copyToRange = copyToRange.Worksheet.Cells(copyToRange.Worksheet.UsedRange.Rows.Count, "A").Offset(1)
            DoEvents
        End If
        fileName = Dir
    Wend
       
    Application.ScreenUpdating = True
   
    MsgBox "Done"
   
End Sub
Thanks so much for your time John_w!

The only thing that needed changing was

From:
If InStr(1, fileName, "_" & Format(Date, "YYYYMMDD") & "_") Then
To:
If InStr(1, fileName, "_" & 20220318 & "_") Then

Ideally that date would be the previous weekday to day (Today-1 Weekday) each time (will see if I can get it to reference a cell within the sheet that has the date in it)

Thanks again for taking the time to look at this much appreciated.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,478
For the previous weekday:
VBA Code:
        If InStr(1, fileName, "_" & Format(Application.WorksheetFunction.WorkDay(Date, -1), "YYYYMMDD") & "_") Then
 

Pumper

Board Regular
Joined
Sep 12, 2013
Messages
78
Office Version
  1. 2016
For the previous weekday:
VBA Code:
        If InStr(1, fileName, "_" & Format(Application.WorksheetFunction.WorkDay(Date, -1), "YYYYMMDD") & "_") Then
Brilliant, worked a treat!
This has helped automate some really boring processes so thank you very much!

People such as yourself make this website second to none...
 

Forum statistics

Threads
1,172,046
Messages
5,878,878
Members
433,380
Latest member
Hadfield

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