title: Looping through multiple Sheets in Multiple Workbooks
body: <p>My Macro needs to run through this range in the worksheet "AtualizaABS" that contains the data necessary for the Macro to work:</p>
  
  <p><a href="https://i.stack.imgur.com/KUAJk.png" rel="nofollow"><img src="https://i.stack.imgur.com/KUAJk.png" alt="enter image description here"></a></p>
  
  <ol>
  <li><p>The Macro must check Column F in the range to identify the name of sheet in the current workbook where it is going to paste data (Variable "Destino" in the code).</p></li>
  <li><p>Once it has done that, the Macro proceeds to open a new folder in which it is going to search for the workbook which name matches the value in Column E (Variable "ABSid" in the code). </p></li>
  <li><p>After identifying the workbook, the Macro must copy all of the cells of the sheet which name matches the values in Column G (Variable "Dados" in the code) and then paste the data from the newly opened workbook into the original one (exactly in the sheet determined by variable "Destino" and column F).</p></li>
  </ol>
  
  <p>The code works for the first row of the range, but when it comes to looping through the other criteria in the sheet "AtualizaABS" and the other Workbooks to be opened, it fails (even though I used "For each" commands). </p>
  
  <p>How could I make the Macro loop through the rows in my range and then through the workbooks in the folder determined by the code?</p>
  
  <pre><code>Sub CopyThenPaste()
  
  Dim wb1 As Workbook
  Dim wb2 As Workbook
  Dim Sheet As Worksheet
  Dim PasteStart As Range
  On Error GoTo Errorcatch
  
  'States the number of the last row thtat contains relevant information to the Macro
  ultima_linha = Range("e2", Range("e2").End(xlDown)).Rows.Count
  
  'Selects the data to be used in the Macro
  Worksheets("AtualizaABS").Activate
  For i = 2 To ultima_linha + 1
  Destino = ActiveSheet.Cells(i, 6).Value
  Dados = ActiveSheet.Cells(i, 7).Value
  ABSid = ActiveSheet.Cells(i, 5).Value
  
  'Selects all of the cells of the worksheet that is going to be updated
      Set wb1 = ActiveWorkbook
      For Each Sheet In wb1.Worksheets
      Set PasteStart = Worksheets(Destino).[A1]
      Sheets(Destino).Select
      Cells.Select
  
  'Asks the user what is the folder where VBA should look for the Workbook with the new information
      With Application.FileDialog(msoFileDialogFolderPicker)
      .Title = "Por favor escolha uma pasta"
      .AllowMultiSelect = False
      If .Show = -1 Then Pasta = .SelectedItems(1)
      End With
  
  
  'Opens the new workbook, copies and then pastes the data in the current Workbook
      For Each wb2 In Workbooks
      Set wb2 = Workbooks.Open(Filename:=Pasta &amp; "\" &amp; ABSid &amp; ".xls")
      wb2.Sheets(Dados).Select
      Cells.Select
      Selection.Copy
      wb1.Worksheets(Destino).Paste Destination:=PasteStart
  
      Application.CutCopyMode = False
      wb2.Close
  
  
      Next
  
      Next
  
  
  Next
  
  
  Exit Sub
  Errorcatch:
  MsgBox Err.Description
  
  
  End Sub
  </code></pre>
  
  <p>Thanks for the attention.</p>