VBA copy and paste

[CassieL]

Hi everyone, I am new to Macro and I hope you can help me out.

I have 5 tabs "1,2,3,4,5" and I like to copy range B4:Y and paste it to "Total" tab. Each tab has different numbers of row.

How can I do that with Macro.
Please help.

Thanks!

 

[NotoriousPopol]

Could you please be more specific about the range of the data you're trying to copy? Is it Columns B to Y, starting row 4, for every tab?

 

[CassieL]

Sorry for the confusion. And yes, Coumn B to Y and starting from row 4 for every tab.

I try to get the code to copy till last row of each tab (cause every tab has different numbers of rows) and paste on "Total"

for example Tab 1 has 50 rows, tab 2 has 34 rows.
The macro will copy the tab 1 50 rows and past on Total tab starting from B4 to B54 and tab 2 data will paste starting from B55.

Thanks for your reply again!

 

[NotoriousPopol]

I assumed the name of your sheets are "1", "2" ... You can try something like :

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("" & 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

 

[CassieL]

Thank you for your help!! I appreciate that a lot.

What if my tab is name in text instead of numbers?

 

[NotoriousPopol]

Well, if their index are 1,2,3,4,5 (meaning they are place in position n°1,2,2,4,5) you can write With ThisWorkbook.Sheets(i).