title: VBA copy multiple sheets and paste to another worksheet
body: <p>I tried to make a macro to go through every sheet and on each sheet, copy to the last row from B4:Y, and paste it to a sheet named "Total" starting from B4. </p>
  
  <p><strong>For example:</strong> </p>
  
  <blockquote>
    <p>I have 5 sheets named: a, b, c, d, e. My macro should copy and paste
    50 rows from "a" at B4:Y54 and 60 rows from "b" at B55:Y115...and so
    on.</p>
  </blockquote>
  
  <p>I have tried looking up online but I can't get my code to work. Please help and thank you very much! </p>
  
  <pre><code>Sub TransferData()
      Dim LTot As Integer ' represents the line in the total tab
      Dim WsTot As Worksheet ' represents your sheet tab
      Dim i As Integer
  
      Set WsTot = ThisWorkbook.Sheets("Total") 'declare your Total tab
      WsTot.Range("B4:Y10000").Clear ' clear the old data
  
      For i = 1 To 5 ' numbers of your tabs
          With ThisWorkbook.Sheets("" &amp; i)
              .Range(.Cells(4, 2), .Cells(.Range("B10000").End(xlUp).Rows, 25)).Copy WsTot.Cells(LTot, 2)
              LTot = LTot + .Range("B10000").End(xlUp).Rows - 4
          End With
      Next
  
  End Sub
  </code></pre>