Collecting information from a variety of sheets 1

[ChrisDesign]

Hello,

I have a problem which I'm working on that has gotten rather involved. I have multiple sheets with a column of names and a column of quantities on each, and I'd like to create a macro that will bring it all together to give me a grand total on a first page.

A couple of problems have been stumping me. First, there is a variable number of pages, and a variable number of equipment items. Second, the same equipment item can appear on multiple pages, in which case I'd like it to add the subtotals together. Finally, there is a fair amount of white space between equipment items which cannot be removed.


If anyone has any suggestions, please let me know. Right now I'm working on a couple of loops, one going through each sheet, and one going through the rows. My plan is to have it all added to an array, which is checked on each iteration, but I'm not exactly good with the syntax and methods of moving from sheet to sheet and adding the information into a single array.

Thanks for the help

 

[Gavona]

If each sheet has the same structure it may be easier to copy all the data to a single sheet and process it there. Loop through all sheets in the worksheets collection.
At its simplest once all the data is in a single worksheet you could summarise using a pivottable.

Gavin

 

[SkipVought]

Hi,

Ditto on bringing the data together into one sheet (Table). Check out a loop like...

dim ws as worksheet, wsSummary as worksheet
wsSummary = Sheets("The Summary Sheet")
for each ws in worksheets
  with wsSummary 
    if ws.name <> .name then 
       ws.[a1].currentregion.copy _
         .cells(.[a1].currentregion.rows.count+1, "A")
    end if
  end with
next

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[Gavona]

I must admit that I find the with.....end with bit of that code impedes clarity. Probably a reflection of the level of my programming experience!
I would prefer
[red]if ws.name <> wsSummary.name then[/red]
That said I will find the code useful so have a star!

Thanks,

Gavin

 

[Gavona]

Small correction:
[red]Destination:=[/red].Cells(.[a1].CurrentRegion.Rows.Count + 1, "A")

Gavin

 

[SkipVought]

[red]Destination:=[/red] is not required.

Interesting, I find that the use of With...End With, greatly adds to the clarity of code, but I admit, it is often a matter of personal taste. I like the look & feel of structured code.

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[Gavona]

The code would not compile for me without the Destination:= bit. If you exclude that don't you need to put brackets around the destination?

Regards,

Gavin

 

[SkipVought]

I would have sworn that I had used that WITHOUT the Destination:= parameter.

I did catch another error that i made...

Dim ws As Worksheet, wsSummary As Worksheet[b]
Set[/b] wsSummary = Sheets("The Summary Sheet")
For Each ws In Worksheets
  With wsSummary
    If ws.Name <> .Name Then
       ws.[a1].CurrentRegion.Copy _
         [.Cells(.[a1].CurrentRegion.Rows.Count + 1, "A")]
    End If
  End With
Next

So sorry


Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}