Macro to pull date from multiple workbooks in a single workbook

[omeirm]

Hello Everyone, I am trying to create a macro that will pull all the data from 80+ files into one sheet. In each of these 80 files they have individual names (ex: Upload_ABC, Upload_DEF,...) The date is in the 3rd tab of each sheet from A:T. I need a macro where I can put in the individual files in a top sheet. I can attach the end file template where the date needs to go in, just not sure how to attach it here. Any help is appreciated.

Thank you
Omeir

 

[pmich]

Re: Help with a macro to pull date from multiple workbooks in a single workbook

1. You have stated the names 'Upload_ABC' and 'Upload_DEF'. Are these names of sheets?
2. You say that 'The DATE is in the 3rd tab of each SHEET'. Do you mean that 'The DATA is in the 3rd tab of each FILE'?
3. You have mentioned that you want to put in the individual files in a top sheet. Do you mean that the data in a particular sheet in file 1 should be pulled and pasted in, say for example, 'AllData' sheet in say for example, 'FullData' File. Then the data in a particular sheet in file 2 should be pulled and pasted in the same 'AllData' sheet below the previous data in 'FullData' File.. This process should be followed for all the files. Is this what you need?
4. Do you think that the rows in 'AllData' Sheet will be sufficient to paste the data from all the files?


The following posts in this forum are similar to your query. This is just for your information.
Copying data from one worksheet to another depending on variable
Copy Data from 1 workbook to another workbook and paste it below
Macro to Combine Data from Different Workbooks into One Workbook

 

[omeirm]

Re: Help with a macro to pull date from multiple workbooks in a single workbook

Thank you for your response.

1) correct, this is the naming convention of the files and are about 80+ files that have unique names beginning with 'upload_...'

2) Yes, I meant the Data is in the 3rd tab of each file. This 3rd tab is named as 'formatted for upload'

3) I would like all the 'formatted for upload' data from these 80+ files into one file.

4) I'm sorry but I'm not understanding what you mean by this. I have a template which has the headers the same as the formatted for upload tab from these 80+ files. I hope this makes sense.

I agree there may have been similar requests from others. I had tried searching but I was not able to find much. If you can help direct me to a similar query in the past, I can use that.

Thanks again for your response and help

 

[mumps]

Re: Help with a macro to pull date from multiple workbooks in a single workbook

Place the following macro in a regular module in your destination workbook. Change the path in the code to match the folder path where your source files are stored. It assumes that your source files are the only files in that folder and that they all have an "xlsx" extension. It also assumes that the destination sheet is named "AllData".

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Dim LastRow As Long
    Const strPath As String = "C:\Test\" 'change folder path to suit your needs
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            LastRow = .Sheets("formatted for upload").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets("formatted for upload").Range("A2:T" & LastRow).Copy wkbDest.Sheets("AllData").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[pmich]

Re: Help with a macro to pull date from multiple workbooks in a single workbook

To : mumps

First of all I would like to thank you for the code.
Though you say in your page https://www.mrexcel.com/forum/members/mumps.html that you are still practicing, your code is so short and so powerful. Thanks.
The code 'strExtension = Dir(strPath & "*.xlsx")' is not within your Do While Loop.
I will be thankful to you if you can explain how 'strExtension = Dir' which is above 'Loop' gets the next files name?
Hope this forum doesn't object you from explaining this to me.

To: omeirm

Ref : 1. I asked 'Are these names of sheets?' You have replied saying 'Correct'. But, you continued saying that these are names of files. Anyway 'mumps' has given a simple and useful code which will open any xlsx file without using the file name.
Ref : 3. In the code given above by 'mumps', all the data from the desired sheet are pulled and posted in AllData 'FILE' in AllData 'SHEET' one below the other. You have mentioned that you want all the data in one 'FILE'. Do you expect the result of the code of 'mumps' which populates one 'SHEET'? Or.. do you want the data from the source files to be posted in AllData 'FILE' in different 'SHEETS'?

 

[mumps]

Re: Help with a macro to pull date from multiple workbooks in a single workbook

@pmich: Thank you for your feedback. The code 'strExtension = Dir(strPath & "*.xlsx")' sets the directory of the source files that need to be opened. It is not within the Do While Loop because the line 'strExtension = Dir' at the end of each loop serves the purpose of setting the search for all xlsx files in the same directory.

 

[omeirm]

Re: Help with a macro to pull date from multiple workbooks in a single workbook

First, I would like thank both of you.

To: pmich & mumps

I apologize, these are the names of the files and not sheets. within these files, I need the data from the third tab labeled as "Formatted for Upload". I tried to edit the macro and I got an error for Runtime error '9', "Subscript of out range" (Highlighted in Red). Below is the macro.

Sub CopyRange()
Application.ScreenUpdating = False
Dim wkbDest As Workbook
Dim wkbSource As Workbook
Set wkbDest = ThisWorkbook
Dim LastRow As Long
Const strPath As String = "X:\SCP\2016\4q\uploads" 'change folder path to suit your needs
ChDir strPath
strExtension = Dir(strPath & "*.xlsx")
Do While strExtension <> ""
Set wkbSource = Workbooks.Open(strPath & strExtension)
With wkbSource
LastRow = .Sheets("Formatted for upload").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
.Sheets("Formatted for upload").Range("A2:T" & LastRow).Copy wkbDest.Sheets("AllData").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
.Close savechanges:=False
End With
strExtension = Dir
Loop
Application.ScreenUpdating = True
End Sub

I would like to have all the data in one final file and one final sheet. Is there a way to pull the data using filename. reason is week 1 there may be 40 upload files, then week 2, 40 more may be added. I don't want to have to double the data for the files that are already compiled into this final file.

Thanks again.

 

[mumps]

Re: Help with a macro to pull date from multiple workbooks in a single workbook

Is the destination sheet named "AllData"? If you want to add the data from each week's uploaded files, the easiest way would be to save each week's files to a different folder and change the folder path in the code to match each time you run the macro. Instead of changing the code each week, the macro could bring up a window where you can select the wanted folder. If you must save all the weeks' files in the same folder, another alternative would be to clear all the previous data from the destination sheet and have the macro copy the data from all the files in the folder. This would eliminate the duplication of data. However, as the number of files grows, it will take more time for the macro to run.

 

[omeirm]

Re: Help with a macro to pull date from multiple workbooks in a single workbook

Mumps- You are great, It worked, I did not have the sheet named as "AllData". However I am having one issue. these reports are linked and when I run this macro, I get the message "This workbook contains links to one of more external sources that could be unsafe." I basically have to click "Don't Update" for each file for it continue working. Is there a way around this?

Thanks again for your help

 

[mumps]

Re: Help with a macro to pull date from multiple workbooks in a single workbook

Try replacing this line of code:

Set wkbSource = Workbooks.Open(strPath & strExtension)

with this line:

Set wkbSource = Workbooks.Open(strPath & strExtension, UpdateLinks:=False)