title: VBA Copy Data from several Different workbook in a specific worksheet and then paste the data to a specific sheet in current Workbook
body: <p>I am very new to VBA and need some help with copy and paste with different workbook and loop sheet names.</p>
  
  <p>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)</p>
  
  <p><a href="http://i.stack.imgur.com/e1VY0.jpg" rel="nofollow">The Control_Panel Sheet Page looks like this:</a></p>
  
  <p>Here is my Code:</p>
  
  <pre><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 = "'" &amp; Cells(4, "C") &amp; "[&amp; Address &amp;]" &amp; Sheet1 &amp; "'!'" &amp;       Range("B6:DO20")
      A = Worksheets("Sheet" &amp; PasteSheet).Activate
      A.Range("C6:DP20").PasteSpecial
  
        CopyRef2 = "'" &amp; Cells(4, "C") &amp; "[&amp; Address &amp;]" &amp; Sheet2 &amp; "'!'" &amp; Range("B7:LK631").Copy
        ActiveWorkbook.Worksheets(PasteSheet).Range("B23:LK647").PasteSpecial
  
  
       End If
      Next i
     End Sub
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Thank you so much!</p>