Copy and Paste data from multiple workbooks into one workbook

[abhay_547]

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>

 

[abhay_547]

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.

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

Can anyone help with the above issue.

 

[Fluff]

I haven't read the entire thread, but based on your op, how about this

Sub getFileData()

   Dim TmpWbk As Workbook, tmpsht As Worksheet
   Dim SrcWbk As Workbook, srcsht As Worksheet
   Dim MstSht As Worksheet
   Dim Rng As Range
   
Application.ScreenUpdating = False

   Set MstSht = ActiveSheet
   Set TmpWbk = Workbooks.Open(MstSht.Range("C2") & MstSht.Range("B2"))
   Set tmpsht = TmpWbk.Sheets(1)
   Set srcsht = TmpWbk.Sheets(1)
   
   With MstSht
      For Each Rng In .Range("A4", .Range("A" & Rows.Count).End(xlUp))
         On Error Resume Next
         Set SrcWbk = Workbooks.Open(Rng.Offset(, 1) & Rng)
         On Error GoTo 0
         If Not SrcWbk Is Nothing Then
            Set srcsht = SrcWbk.Sheets(1)
            srcsht.Range(Rng.Offset(, 2)).Copy tmpsht.Range(Rng.Offset(, 3))
            Rng.Offset(, 4) = "File Available. Data Copied"
            SrcWbk.Close False
         Else
            Rng.Offset(, 4) = "File Not Available"
         End If
         Set SrcWbk = Nothing
      Next Rng
   End With
   
End Sub

 

[abhay_547]

I haven't read the entire thread, but based on your op, how about this

Sub getFileData()

   Dim TmpWbk As Workbook, tmpsht As Worksheet
   Dim SrcWbk As Workbook, srcsht As Worksheet
   Dim MstSht As Worksheet
   Dim Rng As Range
   
Application.ScreenUpdating = False

   Set MstSht = ActiveSheet
   Set TmpWbk = Workbooks.Open(MstSht.Range("C2") & MstSht.Range("B2"))
   Set tmpsht = TmpWbk.Sheets(1)
   Set srcsht = TmpWbk.Sheets(1)
   
   With MstSht
      For Each Rng In .Range("A4", .Range("A" & Rows.Count).End(xlUp))
         On Error Resume Next
         Set SrcWbk = Workbooks.Open(Rng.Offset(, 1) & Rng)
         On Error GoTo 0
         If Not SrcWbk Is Nothing Then
            Set srcsht = SrcWbk.Sheets(1)
            srcsht.Range(Rng.Offset(, 2)).Copy tmpsht.Range(Rng.Offset(, 3))
            Rng.Offset(, 4) = "File Available. Data Copied"
            SrcWbk.Close False
         Else
            Rng.Offset(, 4) = "File Not Available"
         End If
         Set SrcWbk = Nothing
      Next Rng
   End With
   
End Sub

I have tested the above, It just opens and closes the source files but doesn't copy paste the date into the template workbook.

 

[Fluff]

How about

Sub getFileData()

   Dim TmpWbk As Workbook, tmpsht As Worksheet
   Dim SrcWbk As Workbook, srcsht As Worksheet
   Dim MstSht As Worksheet
   Dim Rng As Range
   
Application.ScreenUpdating = False

   Set MstSht = ActiveSheet
   Set TmpWbk = Workbooks.Open(MstSht.Range("C2") & MstSht.Range("B2"))
   
   With MstSht
      For Each Rng In .Range("A3", .Range("A" & Rows.Count).End(xlUp))
         On Error Resume Next
         Set SrcWbk = Workbooks.Open(Rng.Offset(, 1) & Rng)
         On Error GoTo 0
         If Not SrcWbk Is Nothing Then
            Set srcsht = SrcWbk.Sheets(Rng.Offset(, 2).Value)
            srcsht.Range(Rng.Offset(, 3).Value).Copy TmpWbk.Sheets(Rng.Offset(, 4).Value).Range(Rng.Offset(, 5).Value)
            Rng.Offset(, 4) = "File Available. Data Copied"
            SrcWbk.Close False
         Else
            Rng.Offset(, 6).Value = "File Not Available"
         End If
         Set SrcWbk = Nothing
      Next Rng
   End With
   
End Sub

This is based on a data layout like

Excel 2013 32 bit
	A	B	C	D	E	F	G
1	11/21/2017	Template Filename:	template.xlsx	Template File Path	c:\users\template\
2	Source File Names	Source File Paths	Source File worksheetname	Source File Data Range	Template File worksheet name	Template File Paste Range	File Status
3	File A_21_11_2017.xlsx	c:\users\folder A\	A_data	A1:A30	Temp_A	C1:C30	File Available. Data Copied
4	File B_21_11_2017.xlsx	c:\users\folder B\	B_data	A5:A20	Temp_B	D5:D20	File Available. Data Copied
5	File C_21_11_2017.xlsx	c:\users\folder C\	C_data	A2:A5	Temp_C	C2:C5	File Available. Data Copied
6	File D_21_11_2017.xlsx	c:\users\folder D\	D_data	E7:E20	Temp_D	F7:F20	File Not Available
7	File E_21_11_2017.xlsx	c:\users\folder E\	E_data	C4:C12	Temp_E	I4:I12	File Available. Data Copied
8	File F_21_11_2017.xlsx	c:\users\folder F\	F_data	B2:B17	Temp_F	K2:K17	File Available. Data Copied
9	File G_21_11_2017.xlsx	c:\users\folder G\	G_data	D1:D18	Temp_G	F1:F18	File Available. Data Copied
10	File H_21_11_2017.xlsx	c:\users\folder H\	H_data	F14:F15	Temp_H	L14:L15	File Not
File