Stacking data from diffferent excel worksheets within the same excel file,

[Ed]

Deal all,

I'd like to automatically stack data which is in an identical format
(ie same number of colums but different column lengths) within the
same excel file but contained in different worksheets. Ideally I'd
like to have the worksheet name (i.e.off the tab) added to the stacked
data.

The worksheet tab names are product based and non-sequential
I am using excel 2003

Any help would be gratefully received

Thanks

Ed

 

[Dave Peterson]

One way:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim RptWks As Worksheet
Dim RngToCopy As Range
Dim DestCell As Range

With ActiveWorkbook
Set RptWks = .Worksheets.Add
Set DestCell = RptWks.Range("a1")
'make output worksheet column A text
RptWks.Range("a1").EntireColumn.NumberFormat = "@"

For Each wks In .Worksheets
With wks
If .Name = RptWks.Name Then
'skip it
Else
'try to reset the lastused cell
Set RngToCopy = .UsedRange
'get the range to copy
Set RngToCopy _
= .Range("a1", .Cells.SpecialCells(xlCellTypeLastCell))
DestCell.Resize(RngToCopy.Rows.Count, 1).Value = .Name
RngToCopy.Copy
DestCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues
DestCell.Offset(0, 1).PasteSpecial Paste:=xlPasteFormats
'get ready for next time
Set DestCell = DestCell.Offset(RngToCopy.Rows.Count, 0)
End If
End With
Next wks
End With
End Sub