Copy Varying Range Sizes from Mutiple Sheets to Main Sheet

Wester · #1 01-28-2010, 09:54 AM

Hi Guru's,

Sorry if this has been answered in a prior post, but I couldn't seem to search it out. I was wondering if you might be able to help me with a macro? I need to copy a range of cells from multiple sheets, but the cells (rows) may be different sizes in each sheet. However, I have the same title row in each sheet (ie: Fruit) and can place a unique character (

in each range as a stop point (this was the best idea I could think of). I will need to repeat the range (row) copies for different sections, ie: Fruit, Vegetables, Breads & Meat.

Here is an example:
Sheet 1:
Fruit
Apple $1.00 2
Pear $1.10 3
Orange $0.85 1
:

Sheet 2:
Fruit
Apple $1.10 6
Orange $0.90 4
Grape $0.53 9
Lime $0.25 5
:

Sheet 3:
Fruit
Orange $0.80 2
Apple $0.90 5
:

Run Combining Macro, consolidate sections into 1 sheet.
Sheet 4:
Apple $1.00 2
Pear $1.10 3
Orange $0.85 1
Apple $1.10 6
Orange $0.90 4
Grape $0.53 9
Lime $0.25 5
Orange $0.80 2
Apple $0.90 5

Thank you.

Wesley

Domski · #2 01-28-2010, 10:15 AM

Try:

Code:

Sub test()

Dim myWS As Worksheet, writeRow As Long

Sheets("Summary").Range("A1") = "Fruit"

For Each myWS In ActiveWorkbook.Worksheets

    If myWS.Name <> "Sheet4" Then

        With myWS.Range("A1").CurrentRegion
            
            writeRow = Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Row + 1
                     
            .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy Sheets("Summary").Range("A" & writeRow)
            
        End With
        
    End If
    
Next myWS

End Sub

Domski · #3 01-28-2010, 10:37 AM

Expanding on that the attached shows how it might work for each of Fruit, Vegetables, Breads, Meat.

Dom

Wester · #4 01-28-2010, 11:01 AM

Thanks Dom,

Its working but grabbing the entire sheet contents (last row). I need to select a specfic range (rows) of cells from each sheet. I thought a semi-colon could be used as a endpoint for the section. Not worried about section titles (Fruit, Meat, etc) on the summary sheet, I figure I can make a template sheet to suck those titles in. The macro doesn't need to find the sections on the "Summary" sheet either, it can just build each section in order (Fruit, Meat, Vegtable etc.) I figure that I will make a macro for each section title and append to the "Summary" sheet. If my logic is complicated or there is a simplier way of combining the sections of data on each sheet, please lead me in a different direction.

Here is an example:
Sheet 1:
Fruit
Apple $1.00 (2)
Pear $1.10 (3)
Orange $0.85 (1)
:
Meat
Beef $4.00 (5)
Fish $3.20 (3)
Chicken $1.85 (2)
:

Sheet 2:
Fruit
Apple $1.10 (6)
Orange $0.90 (4)
Grape $0.53 (9)
Lime $0.25 (5)
:
Meat
Chicken $2.85 (4)
Beef $4.05 (2)
Fish $3.00 (2)
:

Summary Sheet: (results of macro)
Fruit
Apple $1.00 (2)
Pear $1.10 (3)
Orange $0.85 (1)
Apple $1.10 (6)
Orange $0.90 (4)
Grape $0.53 (9)
Lime $0.25 (5)

Meat
Beef $4.00 (5)
Fish $3.20 (3)
Chicken $1.85 (2)
Chicken $2.85 (4)
Beef $4.05 (2)
Fish $3.00 (2)

-Wesley

Domski · #5 01-28-2010, 11:04 AM

Did you look at the example I attached?

Dom

Wester · #6 01-28-2010, 11:16 AM

I just did, that is much closer. I am not sure if I will have an empty cell between each section of data, hence the Semi-colon to signify an endpoint. But this is awesome, I wouldn't have thought of array. I would have just repeated the macro over for each section.

I guess I could try a macro to delete all semi-colons in column A, which hopefully will only grab each each section vs. the entire sheet.

Or do you have any other ideas around my issue?

-Wesley

Domski · #7 01-28-2010, 11:27 AM

Using the contiguous area around each block of data that the code finds does rely on there being a gab between each area. I get what you mean about using the : and could probably come up with a solution around that but I didn't think it was really necessary.

I've got to go and run around in the cold kicking a football now but post back if you need more help.

Dom

Welcome to the Excel Forum

Forum Statistics