VBA Copy Data from several Different workbook in a specific worksheet and then paste the data to a specific sheet in current Workbook

Question

I am very new to VBA and need some help with copy and paste with different workbook and loop sheet names.

I need to write a Macro to copy data from 100 workbooks (with exactly same formatting and sheet names), need copy from two sheets named "Summary" and "DB", to my current workbook's different sheets. I have set up a Control_Panel Sheet in my current workbook with the name of the copy source files' name and I need to escape the ones are blank (Or delete the sheet that does not have the copy source file, that's even better)

The Control_Panel Sheet Page looks like this:

Here is my Code:

   Option Explicit

   Sub CopyData()
   Dim Address As String
   Dim CopyAddress As String
   Dim CopyRef1 As String
   Dim CopyRef2 As String
   Dim PasteSheet As String
   Dim Sheet1 As String
   Dim Sheet2 As String
   Dim A As Worksheet
   Dim i As Integer


   For i = 8 To 107 Step 1


     If IsEmpty(Cells(i, "D").Value) = False Then

     Sheet1 = "Summary"
     Sheet2 = "DB"

     Address = Cells(i, "D").Value
     PasteSheet = Cells(i, "B").Value


      CopyRef1 = "'" & Cells(4, "C") & "[& Address &]" & Sheet1 & "'!'" &       Range("B6:DO20")
    A = Worksheets("Sheet" & PasteSheet).Activate
    A.Range("C6:DP20").PasteSpecial

      CopyRef2 = "'" & Cells(4, "C") & "[& Address &]" & Sheet2 & "'!'" & Range("B7:LK631").Copy
      ActiveWorkbook.Worksheets(PasteSheet).Range("B23:LK647").PasteSpecial


     End If
    Next i
   End Sub

I do not want to open the copy source file each time when I try to copy the data, and it seems that the code does not recognize the location of the source file. Also, I am not sure if "PasteSheet" name is recognized when I am trying to paste data to each of the sheet.

Please help me to find a way to copy the data from two sheets, named "Summary" and "DB", from multiple workbooks without open them and paste the data to different sheet with the sheet name loops from Cell B8 to B107.

Thank you so much!

What do you mean "it seems that the code does not recognize the location of the source file." — dbmitch
Each time I run the macro, I can see that it copies the active sheet instead of going to the folder and copy the source file. — CCJ
So instead of going to "C:\Users\Desktop\BBB.xlsb" to copy the data from sheet "Summary", it remains in my current workbook and copy the data from whatever the active sheet I'm on. — CCJ
I can't undertstand what you're trying to do. Are you going to overwrite that whole range B23:LK647 in your AAA.XLS with each match in your control panel? Or are you appending? And whatdo you mean my escape the ones that are blank? Those don't have a file that needs to be opened? — dbmitch

V. Wolf V. Wolf · Accepted Answer · 2016-07-25T13:25:32

It is very difficult to see what you are trying. Try to name your workbooks and worksheets like so:

Dim wb as workbook, dim ws as worksheet,
Set wb = ThisWorkbook,
Set ws = wb.Worksheets("thenameofyourworksheet")

And then work with ws.cells(4, "C") etc. At least one error I can see in your code:

Your both variables CopyRef1/2 contain the Address term, which is(should be) in fact a variable. So the placing of your " " is incorrect or insufficient.

Try ... & "[" & Address & "]"

Pro Tip: Use flags! Do not name variables like Address or so, name it sAddress and flag your type.

VBA Copy Data from several Different workbook in a specific worksheet and then paste the data to a specific sheet in current Workbook

1 Answers