combining spreadsheet tabs
 

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

combining spreadsheet tabs
 

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

Jim Thomlinson


"childothe1980s" wrote:

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

combining spreadsheet tabs
 

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)

"childothe1980s" wrote in message
...
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

combining spreadsheet tabs
 

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

Jim Thomlinson


"Jim Thomlinson" wrote:

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

Jim Thomlinson


"childothe1980s" wrote:

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