Copy and Paste data from multiple workbooks into one workbook

A

abhay_547

Board Regular
Joined
Sep 12, 2009
Messages
179
I have requirement to copy a range of data from multiple workbooks into one workbook, but the ask is not to save the macro in either of the workbooks. So i need a third file which would have the names of the files listed in Cell A4 to A12 (can be more files as well). Cell A2 would consist the date when I select the date the names of the workbooks which are listed in cell A4 to A12 will get updated since they all would have date in their names, once the names are updated I would run the macro which would first open the main template workbook, the name of which would be in Cell B2 and it's path is in C1 and then open each file in loop from cell A4 to A12 and copy the data from the range which is mentioned in the cell C4 to C12 from each source file and paste it into the template file range mentioned in column D4 to D12, for each file the path would be different so the path will be mentioned in Column B .i.e. B4 to B12. Incase the current date file is not available on the path then column E4 to E12 would get populated with text "File not available", if available and the data is copied then "File Available. Data Copied"


Below is the sample structure of the file.


Select Date:
Main Template File Name:
Template File Path:
11/21/2017
template.xlsx
c:\users\template\
Source File Names
Source File Paths
Source File Data Range
Template File Paste Range
File Status
File A_21_11_2017.xlsx
c:\users\folder A\
A1:A30
C1:C30
File Available. Data Copied
File B_21_11_2017.xlsx
c:\users\folder B\
A5:A20
D5:D20
File Available. Data Copied
File C_21_11_2017.xlsx
c:\users\folder C\
A2:A5
C2:C5
File Available. Data Copied
File D_21_11_2017.xlsx
c:\users\folder D\
E7:E20
F7:F20
File Not Available
File E_21_11_2017.xlsx
c:\users\folder E\
C4:C12
I4:I12
File Available. Data Copied
File F_21_11_2017.xlsx
c:\users\folder F\
B2:B17
K2:K17
File Available. Data Copied
File G_21_11_2017.xlsx
c:\users\folder G\
D1:D18
F1:F18
File Available. Data Copied
File H_21_11_2017.xlsx
c:\users\folder H\
F14:F15
L14:L15
File Not Available
File I_21_11_2017.xlsx
c:\users\folder I\
K12:K15
J12:J15
File Available. Data Copied

<tbody>
</tbody>
 
Sort by date Sort by votes
May be a noob comment, but wouldn't the user be overwriting data based on destination ranges from lines 4 and line 6?
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Also, the additional requirement I have is that the data copied from each source workbook/worksheet combination needs to be copied to the consolidating destination workbook in a worksheet named the same as the source file (sans the .xlsx).

MODERATOR - Please advise if you prefer I started a new thread ... this one gives me 98% of what I need, I am looking for the remaining 2%
 
Upvote 0
May be a noob comment, but wouldn't the user be overwriting data based on destination ranges from lines 4 and line 6?
Click to expand...
That could definitely be an issue. In the case of this solution, the coding doesn't look for duplication or data lose. The quality of the data in the table is up to the user that updates it. If more than one user is updating that table, it may be good to add in a check on duplication.

Also, the additional requirement I have is that the data copied from each source workbook/worksheet combination needs to be copied to the consolidating destination workbook in a worksheet named the same as the source file (sans the .xlsx).
Click to expand...
In this case, I would put the left of the workbook's name into a variable to use when identifying the worksheet that the information is being copied to. Such as:
Code: 
sWbName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)
 
Upvote 0
Sorry to continue to impose, but I have been trying to use the code you posted in post #8 .

I am not sure how to incorporate your suggestion for sWbName above. I have already isolated the file name / tab name in a column without the file extension, so I do not need to do the text work you note.

Using the original data elements provided in the first post, assume I have added a column F and cell F4 has File A_21_11_2017 as the intended worksheet tab I want to which I want to post the source data. F5 has File B_21_11_2017 and so on. How do I change the For/Next loop to include the sWbName variable to pull in the cell reference and post to the relevant tab?

I really appreciate your assistance. This will be the final step in consolidating 100 individual workbooks of budget data.
 
Upvote 0
Without knowing your exact situation, you can just stick that bit toward the beginning of the first loop.
Code: 
    For i = 4 To finalRow
        sPath = masterWb.Range("B" & i).Value
        [COLOR=#0000ff]sWbName = masterWb.Range("F" & i).Value[/COLOR]
        sFileName = [COLOR=#0000ff]sWbName & ".xlsx"[/COLOR]
        
        If ThisWorkbook.Sheets(1).Range("E" & i) = "" Then
            Workbooks.Open Filename:=sPath & sFileName
            
            For j = i To finalRow
            
                If Cells(j, 2).Value = sPath And Cells(j, 1).Value = sFileName Then
                    Range(masterWb.Range("C" & j).Value).Copy Destination:=templateWb.Range(masterWb.Range("D" & j).Value)
                    master.Range("E" & j).Value = "File Available. Data Copied"
                    
                End If
                
            Next j
            Workbooks(templateWb.Range("A" & i).Value).Close SaveChanges:=False
            
        End If
        
NextWB:
    Next i

The "templateWb" references may need to be tweaked due to the sheet name changing, though. templateWb could just be the workbook and not the workbook and sheet.
Code: 
Set templateWb = ActiveWorkbook
 
Upvote 0
AFPathfinder said:
It can if we added additional code to accommodate the additional requirements, but it depends on how that will be presented in the table. If the rows that correspond with the same workbook are not in consecutive rows, a nested loop would be needed to check for that. I would probably add the file path and the workbook name to variables to make it easier to code the comparison. Something like this:
Code: 
Sub WorkbookLoop()
    Dim i As Integer
    [COLOR=#0000ff]Dim j As Integer[/COLOR]
    Dim finalRow As Integer
    [COLOR=#0000ff]Dim masterWb As Worksheet[/COLOR]
    Dim templateWb As Worksheet
    [COLOR=#0000ff]Dim sPath As String
    Dim sFileName As String[/COLOR]
    
    [COLOR=#0000ff]Set masterWb = ThisWorkbook.Sheets(1)
    Set templateWb = Workbooks("Template").Sheets(1)[/COLOR]
    
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    On Error GoTo MissWB
    For i = 4 To finalRow
        [COLOR=#0000ff]sPath = masterWb.Range("B" & i).Value
        sFileName = masterWb.Range("A" & i).Value[/COLOR]
        
        [COLOR=#0000ff]If ThisWorkbook.Sheets(1).Range("E" & i) = "" Then[/COLOR]
            Workbooks.Open Filename:=[COLOR=#0000ff]sPath & sFileName[/COLOR]
            
            [COLOR=#0000ff]For j = i To finalRow
            
                If Cells(j, 2).Value = sPath And Cells(j, 1).Value = sFileName Then[/COLOR]
                    Range(masterWb.Range("C" & j).Value).Copy Destination:=templateWb.Range(masterWb.Range("D" & j).Value)
                    master.Range("E" & j).Value = "File Available. Data Copied"
                    
                [COLOR=#0000ff]End If
                
            Next j[/COLOR]
            Workbooks(templateWb.Range("A" & i).Value).Close SaveChanges:=False
            
        End If
        
NextWB:
    Next i
    
    On Error GoTo 0
    
    MsgBox "Done."
    
    [COLOR=#0000ff]Set masterWb = Nothing
    Set templateWb = Nothing[/COLOR]
    
    Exit Sub
    
MissWB:
    masterWb.Range("E" & i).Value = "File Not Available"
    Resume NextWB
End Sub

If you can control the table, you could make due with an If statement that checks for the same workbook in following rows.
Click to expand...


I have made few changes to the table structure of the macro file, below is the revised grid which includes the source file worksheet name and the template file worksheet name. I don't want to hard code the file name or sheet names of source and template file in the code, even they should be referred through cell in macro file. I have updated the code to refer to the cells but for some reason it's not working. can you check and advise what's going wrong.

Below is the revised grid:
11/21/2017Template Filename:template.xlsxTemplate File Pathc:\users\template\
Source File NamesSource File PathsSource File worksheetnameSource File Data RangeTemplate File worksheet nameTemplate File Paste RangeFile Status
File A_21_11_2017.xlsxc:\users\folder A\A_dataA1:A30Temp_AC1:C30File Available. Data Copied
File B_21_11_2017.xlsxc:\users\folder B\B_dataA5:A20Temp_BD5:D20File Available. Data Copied
File C_21_11_2017.xlsxc:\users\folder C\C_dataA2:A5Temp_CC2:C5File Available. Data Copied
File D_21_11_2017.xlsxc:\users\folder D\D_dataE7:E20Temp_DF7:F20File Not Available
File E_21_11_2017.xlsxc:\users\folder E\E_dataC4:C12Temp_EI4:I12File Available. Data Copied
File F_21_11_2017.xlsxc:\users\folder F\F_dataB2:B17Temp_FK2:K17File Available. Data Copied
File G_21_11_2017.xlsxc:\users\folder G\G_dataD1:D18Temp_GF1:F18File Available. Data Copied
File H_21_11_2017.xlsxc:\users\folder H\H_dataF14:F15Temp_HL14:L15File Not Available
Below is the revised code:

Code: 
Sub WorkbookLoop()    Dim i As Integer
    Dim j As Integer
    Dim finalRow As Integer
    Dim masterWb As Worksheet
    Dim templateWb As Worksheet
    Dim sPath As String
    Dim sFileName As String
    
    Set masterWb = ThisWorkbook.Sheets(1)
    Set templateWb = Workbooks("Template").Sheets(1)
    
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    On Error GoTo MissWB
    For i = 4 To finalRow
        sPath = masterWb.Range("E" & i).Value
        sFileName = masterWb.Range("C" & i).Value
        
        If ThisWorkbook.Sheets(1).Range("G" & i) = "" Then
            Workbooks.Open Filename:=sPath & sFileName
            
            For j = i To finalRow
            
                If Cells(j, 2).Value = sPath And Cells(j, 1).Value = sFileName Then
                    Range(masterWb.Range("D" & j).Value).Copy Destination:=templateWb.Range(masterWb.Range("F" & j).Value)
                    master.Range("G" & j).Value = "File Available. Data Copied"
                    
                End If
                
            Next j
            Workbooks(templateWb.Range("A" & i).Value).Close SaveChanges:=False
            
        End If
        
NextWB:
    Next i
    
    On Error GoTo 0
    
    MsgBox "Done."
    
    Set masterWb = Nothing
    Set templateWb = Nothing
    
    Exit Sub
    
MissWB:
    masterWb.Range("G" & i).Value = "File Not Available"
    Resume NextWB
End Sub
File Available. Data Copied









<colgroup><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
It would be changed in this line:
Code: 
Set templateWb = Workbooks("Template").Sheets(1)

Since it's being loaded to the templateWb variable, we'll want to change the reference where the variable is being defined. Something like this:
Code: 
Set templateWb = Workbooks([COLOR=#0000FF]masterWb.Range("C1").Value[/COLOR]).Sheets([COLOR=#0000FF]masterWb.Range("E1").Value[/COLOR])
I don't know where the sheet name is placed, so the "E1" range reference probably isn't correct.
 
Last edited:
Upvote 0
AFPathfinder said:
It would be changed in this line:
Code: 
Set templateWb = Workbooks("Template").Sheets(1)

Since it's being loaded to the templateWb variable, we'll want to change the reference where the variable is being defined. Something like this:
Code: 
Set templateWb = Workbooks([COLOR=#0000FF]masterWb.Range("C1").Value[/COLOR]).Sheets([COLOR=#0000FF]masterWb.Range("E1").Value[/COLOR])
I don't know where the sheet name is placed, so the "E1" range reference probably isn't correct.
Click to expand...



The sheet name in the template workbook could be different for each range as well, hence i had listed all the template workbook sheet names in column E starting row 4, if we make it static .i.e. refer it to cell E1 then it would be a problem. how can we refer to column E starting row 4 and loop.



ffb35affa8
 
Upvote 0
Maybe this one will work:
Code: 
Sub WorkbookLoop()
    Dim i As Integer
    Dim j As Integer
    Dim finalRow As Integer
    Dim masterWb As Worksheet
    Dim sPath As String
    Dim sFileName As String
    
    Set masterWb = ThisWorkbook.Sheets(1)
    
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    On Error GoTo MissWB
    For i = 4 To finalRow
        sPath = masterWb.Range("B" & i).Value
        sFileName = masterWb.Range("A" & i).Value
        
        If ThisWorkbook.Sheets(1).Range("E" & i) = "" Then
            Workbooks.Open Filename:=sPath & sFileName
            
            For j = i To finalRow
            
                If Cells(j, 2).Value = sPath And Cells(j, 1).Value = sFileName Then
                    Range(masterWb.Range("C" & j).Value).Copy _
                        Destination:=Workbooks(masterWb.Range("C1").Value).Sheets(masterWb.Range("E" & i).Value).Range(masterWb.Range("D" & j).Value)
                    master.Range("E" & j).Value = "File Available. Data Copied"
                    
                End If
                
            Next j
            Workbooks(sFileName).Close SaveChanges:=False
            
        End If
        
NextWB:
    Next i
    
    On Error GoTo 0
    
    MsgBox "Done."
    
    Set masterWb = Nothing
    
    Exit Sub
    
MissWB:
    masterWb.Range("E" & i).Value = "File Not Available"
    Resume NextWB
End Sub
 
Last edited:
Upvote 0
AFPathfinder said:
Maybe this one will work:
Code: 
Sub WorkbookLoop()
    Dim i As Integer
    Dim j As Integer
    Dim finalRow As Integer
    Dim masterWb As Worksheet
    Dim sPath As String
    Dim sFileName As String
    
    Set masterWb = ThisWorkbook.Sheets(1)
    
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    On Error GoTo MissWB
    For i = 4 To finalRow
        sPath = masterWb.Range("B" & i).Value
        sFileName = masterWb.Range("A" & i).Value
        
        If ThisWorkbook.Sheets(1).Range("E" & i) = "" Then
            Workbooks.Open Filename:=sPath & sFileName
            
            For j = i To finalRow
            
                If Cells(j, 2).Value = sPath And Cells(j, 1).Value = sFileName Then
                    Range(masterWb.Range("C" & j).Value).Copy _
                        Destination:=Workbooks(masterWb.Range("C1").Value).Sheets(masterWb.Range("E" & i).Value).Range(masterWb.Range("D" & j).Value)
                    master.Range("E" & j).Value = "File Available. Data Copied"
                    
                End If
                
            Next j
            Workbooks(sFileName).Close SaveChanges:=False
            
        End If
        
NextWB:
    Next i
    
    On Error GoTo 0
    
    MsgBox "Done."
    
    Set masterWb = Nothing
    
    Exit Sub
    
MissWB:
    masterWb.Range("E" & i).Value = "File Not Available"
    Resume NextWB
End Sub
Click to expand...

Still not working, when i run the macro it shows Done msg box, i commented out the Done msgbox and on error go to 0 in the code to see the error but it doesn't show any error.


I believe there is some issue with the references, below is my understanding about the references which you have given in the code.


- masterwb = Macro file which consists of all source file and target file information with macro module
- Template/target file = the file which is the target file in which the data will be copied into, can have multiple worksheets hence cannot hardcode the sheetname or order.
- source files = the files from which the data would be copied and pasted into template file.


- sPath = Source file path column which is in the macro file column B
- sFilename = Source file name column which is in the macro file Column A
- E column consists of Target/Template worksheet name but code says: If ThisWorkbook.Sheets(1).Range("E" & i) = "" Then
- C column consists of Source workbook's worksheet name but code says: Range(masterWb.Range("C" & j).Value).Copy, i believe it should refer to the column D for copy range.
- in this code line we are referring to both source and target/template file columns .i.e. column C and E and the range is column D, i believe the destination should be target/target file references that is column E and F : Destination:=Workbooks(masterWb.Range("C1").Value).Sheets(masterWb.Range("E" & i).Value).Range(masterWb.Range("D" & j).Value)

can you please check and advise.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Copying files to a newly created folder named from data within spreadsheet
Replies
2
Views
207
AndyEd
A
I
  • Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
207
Insurance Dave
I
H
  • Question
VBA split file based and send splitted file in separate emails
Replies
0
Views
143
harmless92
H
H
  • Solved
Copy File Names where Month and Year Matches
Replies
6
Views
258
howard
H
P
  • Solved
VBA to open file using Cell for the path
Replies
4
Views
549
Peter Davison
P

Forum statistics

Threads
1,214,659
Messages
6,120,786
Members
448,992
Latest member
prabhuk279

Share this page

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
Back
Top