title: Looping through dynamic sheet names
body: <p>I am looping through a bunch of files in my folder and trying to copy a static column and paste to a master sheet. However every sheet I am looping through is a different name.</p>
  
  <p>I believe this part of the code has to be changed:</p>
  
  <pre><code>xlsFiles.Sheets("Sheet3").Columns("20").Copy Destination:=wsMaster.Sheets("Sheet1").Range("A" &amp; r).Offset(1, 0).
  </code></pre>
  
  <p>What can I use in place of sheets("Sheet3") ? </p>
  
  <p>Here is the full code:</p>
  
  <pre><code>Option Explicit
  Dim wsMaster As Workbook, csvFiles As Workbook
  Dim Filename As String
  Dim File As Integer
  Dim r As Long
  
  Public Sub Consolidate()
  
      With Application
          .ScreenUpdating = False
          .EnableEvents = False
      End With
  
      With Application.FileDialog(msoFileDialogOpen)
          .AllowMultiSelect = True
          .Title = "Select files to process"
          .Show
  
          If .SelectedItems.Count = 0 Then Exit Sub
  
          Set wsMaster = ActiveWorkbook
  
          For File = 1 To .SelectedItems.Count
  
              Filename = .SelectedItems.Item(File)
  
  
             If Right(Filename, 5) = ".csv*" Then
      Set csvFiles = Workbooks.Open(Filename, 0, True)
      r = wsMaster.Sheets("Sheet1").UsedRange.Rows.Count
      csvFiles.Sheets(1).Columns("col name").Copy Destination:=wsMaster.Sheets("Sheet1").Range("A" &amp; r).Offset(1, 0)
      csvFiles.Close SaveChanges:=False 'close without saving
              End If
  
  
          Next File 'go to the next file and repeat the process
  
      End With
  
      Set wsMaster = Nothing
      Set csvFiles = Nothing
  
      With Application
          .ScreenUpdating = True
          .EnableEvents = True
  
      End With
  
  End Sub
  </code></pre>