combining spreadsheet tabs

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello:

I have a spreadsheet of about 30 tabs. Each tab contains a different number
of rows but the same number of columns (four columns).

Is it possible to combine these tabs into one spreadsheet with four columns?
Probably not, but I thought that I would check with you all out in
cyberspace.

Thanks!

childofthe1980s
 
To do it in code is fairly straight forward, but to do it via formula is not
going to work too well for you. If you wnat some code just RSVP...
 
VBA would be simple.

Sub Test()
Dim iLastRow As Long
Dim i As Long

i = 1
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> "Master" Then
iLastRow = sh.Cells(shRows.Count, "A").End(xlUp).Row
sh.Range("A1").Resize(iLastRow, 4).Copy _
Worksheets("Master").Cells(i, "A")
i = i + iLastRow
End If
Next sh

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Here is the code if you need it... It assumes that on all 30 tabs the A
column will be populated to the last row of data...

Sub MakeOneSheet()
Dim wksNew As Worksheet
Dim wks As Worksheet

Set wksNew = Worksheets.Add

For Each wks In Worksheets
With wks
If .Name <> wksNew.Name Then
.Range(.Range("D1"), .Cells(Rows.Count, "A").End(xlUp)).Copy _
Destination:=wksNew.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
End With
Next wks
End Sub
 
Back
Top