combining spreadsheet tabs

[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

 

[Guest]

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...

 

[Bob Phillips]

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)

 

[Guest]

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