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
If the macro was reaching the MsgBox, I'd say you're right about the reference. I think it might be this one that is causing the problem:
Code: 
If ThisWorkbook.Sheets(1).Range("E" & i) = "" Then
Isn't the new column for status in column G? If the values in column E are never empty, the stuff inside that If statement won't run, so it just skips over all the rows.

On a side note, do you know how to debug by stepping through the code (insert a break line in the code and when the code stops, press F8 key to execute each line)? If the table changes every now and then, you may find yourself digging into the code and making changes each time it does.

If you have two monitors, it helps to step through each line of code and see what is happening (VB Editor on one and Excel on the other).
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
AFPathfinder said:
If the macro was reaching the MsgBox, I'd say you're right about the reference. I think it might be this one that is causing the problem:
Code: 
If ThisWorkbook.Sheets(1).Range("E" & i) = "" Then
Isn't the new column for status in column G? If the values in column E are never empty, the stuff inside that If statement won't run, so it just skips over all the rows.

On a side note, do you know how to debug by stepping through the code (insert a break line in the code and when the code stops, press F8 key to execute each line)? If the table changes every now and then, you may find yourself digging into the code and making changes each time it does.

If you have two monitors, it helps to step through each line of code and see what is happening (VB Editor on one and Excel on the other).
Click to expand...


Got it. I have changed the column reference to the G in the below code line. but now it shows error message that the file not found for the file which is not available. Actually column G wouldn't have the status of the unavailable files already updated, it will be completely blank when you start running the macro, if the macro doesn't find the source file which is mentioned in column A, on the path which is mentioned in column B then instead of showing error, it should populate "File Not Available" text in the column G for that row and move to the next row. but I believe right now, it's actually checking the column G for the file availability status .i.e. if it's blank then it will attempt to open the file and copy and paste the data but if it's not blank then it would skip it. We wouldn't know if the files are available for the day since multiple users are savings those files in multiple shared paths hence instead of opening each folder and checking, the macro checks it .i.e. if it's unable to find the file when trying to open then instead of showing error it simply populates the column G with "File not available". Now I am aware that this could happen even if the naming convention of the source file is slightly different from what is mentioned in the macro file column A but that will be taken care by user who is saving the source file as he will be asked to stick to the standard naming convention with no changes to be saved daily on the listed path.
 
Upvote 0
I believe you received that error because you removed the error handler code, right? I suppose the proper way to do that is to create a separate function "IsFound" that returns a boolean based on if the file exists and can be opened.

The error handler is implicit as it doesn't check for that particular error and It doesn't specify the difference between an input error in the table and a legitimate "file doesn't exist".
 
Upvote 0
AFPathfinder said:
I believe you received that error because you removed the error handler code, right? I suppose the proper way to do that is to create a separate function "IsFound" that returns a boolean based on if the file exists and can be opened.

The error handler is implicit as it doesn't check for that particular error and It doesn't specify the difference between an input error in the table and a legitimate "file doesn't exist".
Click to expand...


Ok. here is the updated code, I have updated the column reference as per the below table (which is final), I know that I had commented out the error handler earlier but i made the change back to the earlier code but still i was receiving the error msg for file not found first from excel and then when I click ok, it was updating the "File not available" text in column G, hence i had added Application.displayalert as false and then at the end of the code back to true. but it seems that the code is missing the target/template file information, .i.e. it opens the source file from the source file path and copies the data but doesn't open the target/template file and paste the data in it.

here are the details once again (as per the below table snapshot):
C1 = Template/Target File name
E1 = Template/Target File path
Column A starting row 4 : Source file names.
Column B starting row 4: Source file paths
Column C starting row 4: source workbook's worksheet names
Column D starting row 4: source file data range (range which needs to be copied)
Column E starting row 4: Target / Template workbook's worksheet names
Column F starting row 4: Target/Template file paste range
Column G starting row 4: Update the status of the file, if the file is not available then update "File not available" and if file is available post copy and pasting update text "file available. data copied". even if this column already has the values .i.e. "file not available" still it should check for the files because, for example, the user would run the macro at 3 pm when all files are not available so for those files, column G would be updated as file not available but then he gets the confirmation at 4 pm that all files are now available so when he reruns the macro, it would ignore the rows which has values .i.e file not available.




ffb35affa8




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
    Application.DisplayAlerts = False
    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("G" & i) <> "File Available. Data Copied" 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:=Workbooks(masterWb.Range("D1").Value).Sheets(masterWb.Range("E" & i).Value).Range(masterWb.Range("F" & j).Value)
                    master.Range("G" & 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
     Application.DisplayAlerts = True
    Exit Sub
    
MissWB:
    masterWb.Range("G" & i).Value = "File Not Available"
    Resume NextWB
End Sub

can you please help to include the target/template sheet name and paste ranges to the above code which are in column E and F
 
Upvote 0
Try this:
Code: 
Sub WorkbookLoop()
    Dim i As Integer
    Dim j As Integer
    Dim finalRow As Integer
    Dim masterWb As Worksheet
    Dim templateWb As Workbook
    Dim sPath As String
    Dim sFileName As String
    
    Set masterWb = ThisWorkbook.Sheets(1)
    Workbooks.Open masterWb.Range("C1").Value & Range("F1").Value
    Set templateWb = ActiveWorkbook
    
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Application.DisplayAlerts = False
    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("G" & i) <> "File Available. Data Copied" 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.Sheets(masterWb.Range("E" & j).Value).Range(masterWb.Range("F" & j).Value)
                    master.Range("G" & 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
    Application.DisplayAlerts = True
    Exit Sub

    
MissWB:
    masterWb.Range("G" & i).Value = "File Not Available"
    Resume NextWB
End Sub
 
Upvote 0
AFPathfinder said:
Try this:
Code: 
Sub WorkbookLoop()
    Dim i As Integer
    Dim j As Integer
    Dim finalRow As Integer
    Dim masterWb As Worksheet
    Dim templateWb As Workbook
    Dim sPath As String
    Dim sFileName As String
    
    Set masterWb = ThisWorkbook.Sheets(1)
    Workbooks.Open masterWb.Range("C1").Value & Range("F1").Value
    Set templateWb = ActiveWorkbook
    
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Application.DisplayAlerts = False
    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("G" & i) <> "File Available. Data Copied" 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.Sheets(masterWb.Range("E" & j).Value).Range(masterWb.Range("F" & j).Value)
                    master.Range("G" & 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
    Application.DisplayAlerts = True
    Exit Sub

    
MissWB:
    masterWb.Range("G" & i).Value = "File Not Available"
    Resume NextWB
End Sub
Click to expand...


I have changed the cell references in the below line of code:

Code: 
 Set masterWb = ThisWorkbook.Sheets(1)
    Workbooks.Open masterWb.Range("E1").Value & Range("C1").Value
    Set templateWb = ActiveWorkbook

but it still opens the template file and doesn't open the source files from the column A and copy and paste the data into the template file.
 
Upvote 0
Try this one:
Code: 
Sub WorkbookLoop()
    Dim i As Integer
    Dim j As Integer
    Dim finalRow As Integer
    Dim masterWb As Worksheet
    Dim templateWb As Workbook
    Dim sPath As String
    Dim sFileName As String
    
    Set masterWb = ThisWorkbook.Sheets(1)
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row

    Workbooks.Open masterWb.Range("E1").Value & Range("C1").Value
    Set templateWb = ActiveWorkbook
    
    Application.DisplayAlerts = False
    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("G" & i) <> "File Available. Data Copied" 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.Sheets(masterWb.Range("E" & j).Value).Range(masterWb.Range("F" & j).Value)
                    master.Range("G" & 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
    Application.DisplayAlerts = True
    Exit Sub

    
MissWB:
    masterWb.Range("G" & i).Value = "File Not Available"
    Resume NextWB
End Sub
 
Upvote 0
AFPathfinder said:
Try this one:
Code: 
Sub WorkbookLoop()
    Dim i As Integer
    Dim j As Integer
    Dim finalRow As Integer
    Dim masterWb As Worksheet
    Dim templateWb As Workbook
    Dim sPath As String
    Dim sFileName As String
    
    Set masterWb = ThisWorkbook.Sheets(1)
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row

    Workbooks.Open masterWb.Range("E1").Value & Range("C1").Value
    Set templateWb = ActiveWorkbook
    
    Application.DisplayAlerts = False
    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("G" & i) <> "File Available. Data Copied" 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.Sheets(masterWb.Range("E" & j).Value).Range(masterWb.Range("F" & j).Value)
                    masterWb.Range("G" & 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
    Application.DisplayAlerts = True
    Exit Sub

    
MissWB:
    masterWb.Range("G" & i).Value = "File Not Available"
    Resume NextWB
End Sub
Click to expand...

Now it opens the source files as well and switches between template file and source file but doesn't copy any range from source file and doesn't paste any data into the template file range. can you please check the below piece of code again, i believe something is still going wrong.

Code: 
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.Sheets(masterWb.Range("E" & j).Value).Range(masterWb.Range("F" & j).Value)
                    masterWb.Range("G" & j).Value = "File Available. Data Copied"
                    
                End If
                
            Next j
            Workbooks(sFileName).Close SaveChanges:=False
 
Upvote 0
Something might very well be going wrong (error handling might be skipping rows), but I don't have a multi-workbook setup like yours to do the debugging and testing. A solution like this is pretty specific and you'll need to do the debugging on your end to sniff out where the problems might be.
 
Upvote 0
AFPathfinder said:
Something might very well be going wrong (error handling might be skipping rows), but I don't have a multi-workbook setup like yours to do the debugging and testing. A solution like this is pretty specific and you'll need to do the debugging on your end to sniff out where the problems might be.
Click to expand...


It's not skipping any rows, it goes each row after row and switches between both template and source workbook but doesn't select the copy range from the source workbook and also doesn't paste anything into the template workbook. I tried to check it a couple of times from my end but i am unable to figure what's going wrong. I even added the .Paste at the end of the Destination line but it doesn't seem to work. Also it's not updating the text "File Available. Data copied" in the column G but it doesn't update "File Not Available" text as well which means its not encountering any error then what else could be the problem.

Code: 
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.Sheets(masterWb.Range("E" & j).Value).Range(masterWb.Range("F" & j).Value)[B].Paste[/B]
                    master[B]Wb[/B].Range("G" & j).Value = "File Available. Data Copied"
                    
                End If
                
            Next j
            Workbooks(sFileName).Close SaveChanges:=False
 
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
211
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
229
Insurance Dave
I
H
  • Question
VBA split file based and send splitted file in separate emails
Replies
0
Views
153
harmless92
H
H
  • Solved
Copy File Names where Month and Year Matches
Replies
6
Views
270
howard
H
P
  • Solved
VBA to open file using Cell for the path
Replies
4
Views
588
Peter Davison
P

Forum statistics

Threads
1,214,981
Messages
6,122,565
Members
449,089
Latest member
Motoracer88

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