title: Copying ranges from one sheet into another using a loop of sheet names
body: <p>I am attempting to compile tables from 118 sheets into two tables (59 tables each). I have a list of all sheet names within the sheet "<em>Sheet Listing</em>." Currently my code only returns the table from the last sheet, presumably overwriting all the others. For the first set of 59 sheets, the range on every sheet is the same (<code>"A5:F73"</code>). For the next 59 sheets, the number of rows varies between 69 and 68 rows, so I guess the easiest way to deal with that is to write code that would begin a selection at cell A5 (always), select all columns over to column F, then do a row count to determine the last row to copy.  </p>
  
  <p>Below is what I have cobbled together so far for the set of sheets with a known range:  </p>
  
  <pre><code>Sub ConsolData()
  
  Dim sheet_name As Range
  Dim lastrowdata As Long
  
  With Sheets("SB Summary")
      lastrowdata = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
  End With
  
  For Each sheet_name In Sheets("Sheet Listing").Range("D6:D64") 
  If sheet_name.value = "" Then 
      exit For 
  
  Else
      Sheets(sheet_name.value).select 
      Range("A5:F73").Select 
  Selection.Copy
  
  Sheets("SB Summary").select
  Range("B2").Offset(lastrowdata).Select 
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
      :=False, Transpose:=False
  Sheets(sheet_name.value).select
  End If
  Next sheet_name
  
  End Sub
  </code></pre>
  
  <p>I think the problem lies with the line of code specifying the last row of data to paste into:</p>
  
  <pre><code>Range("B2").Offset(lastrowdata).Select
  </code></pre>
  
  <p>Question 1: How do I correctly specify the offset in order to prevent overwriting previously pasted tables?</p>
  
  <p>Question 2: Would it be easier to modify this code to also handle the dynamic ranges of the second set of 59 tables so that it works for this first set of 59 static/known ranges?</p>
  
  <p>Question 3: For each range copied, I'd like to fill in the sheet name into all the cells in column H next to what was just pasted so that I know where it came from. How do I do that?</p>