Re: Combining ranges in Excel

Simon,

Does this help?

Sub ExtractData()
Dim shSummary As Work***
Dim sh As Work***
Dim iRow As Long
Dim iNext As Long

On Error Resume Next
Set shSummary = Worksheets("Summary")
On Error GoTo 0
If shSummary Is Nothing Then
Set shSummary = Worksheets.Add
shSummary.Name = "Summary"
End If
shSummary.Cells.ClearContents

iNext = 1
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> shSummary.Name Then
iRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
sh.Range("A1").Resize(iRow, 10).Copy _
shSummary.Cells(iNext, "A")
iNext = iNext + iRow
End If
Next sh

Set sh = Nothing
Set shSummary = Nothing

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Sime" <me@xxxxxxxx> wrote in message
news:e8qhhk$llh$1$8302bc10@xxxxxxxxxxxxxxxxxxx
After wrestling with this for what seems like hours, I'm afraid I'm
admitting defeat and casting it before the collective minds of this group
for any hints!

What I have is a workbook containing around 50 worksheets, each one
holding
a list of one particular customer's transactions (invoices and payments).
What I need to do is construct a single list of all transactions
(automatically updated if possible, quick to manually update if not), so
that I can filter, summarize and pivot. My main problem is that my ***
names are the names of the customers (rather than Sheet1, Sheet2, etc.) so
if there is no quick method of doing this, I'll have to copy down formulas
and change *** names fifty times. Plus update the list every time I add
a
customer, which I'm trying to avoid.

I'm looking for things like transposing a 3d range to a 2d one, a
R1C1-style
referencing method that works on 3d ranges, a way of pivotting a 3d range,
or any similar method. Even a clever little script would do!

Any thoughts on this would be *really* appreciated.

Many thanks, as ever.
Simon




.