Populating Master list from multiple sheets

[T-rev]

Hope someone can help...

i have generated a list of "Companies on the ASX"
(Australian Stock Exchange)

There are several work sheets (all populated from data queries) of companies within different sectors e.g all companies within the "energy" sector and so on (one sheet for each sector)

i am trying to populate a master list of all the companies from all of the sectors however there are some conditions i am having trouble meeting...

1. it has to reflect the exact data on the "sector page" after the Query has been refreshed.

2. i have left some formated rows to allow for new listings as the Query updates so i need it to copy each company from each page, something like..

"Energy" Copy A2:K2 - A5000:K5000 (stopping at row 2500 if that is where the last company is on that list, then the same for the next sheet.)

Any help would be greatly appreciated

 

[jbeaucaire]

Hmm, assuming the master sheet is called MASTER, something like this:

Option Explicit

Sub AssembleMasterData()
Dim ws As Worksheet
Dim LR As Long      'used to get the last row of data on each data sheet

With Sheets("Master")                   'put the name of your master sheet here
    .UsedRange.Offset(1).ClearContents  'remove data, leave titles in row 1
    
    For Each ws In Worksheets
        If ws.Name <> .Name Then        'skip the master sheet, use all others
            LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
            ws.Range("A2:K" & LR).Copy .Range("A" & .Rows.Count).End(xlUp).Offset(1)
        End If
    Next ws
End With

End Sub

 

[T-rev]

Excellent, thanks very much, however i do also have a Sector Summary sheet which i would not like to be copied, is it possible to exclude this sheet?

sorry, i should have mentioned that earlier.

 

[jbeaucaire]

    For Each ws In Worksheets
        If ws.Name <> .Name And ws.Name <> "Sector Summary" Then        'skip these sheets, use all others
            LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
            ws.Range("A2:K" & LR).Copy .Range("A" & .Rows.Count).End(xlUp).Offset(1)
        End If
    Next ws

 

[T-rev]

hmm... i'm still have trouble with this code.. perhapse i should have been more specific.

1.i would like it to skip over my Market summary page which it is now doing

2. i would like it to find every other sheet (except the Master List and Market Summary, and return the value of the cells in the range, using row count, Beginning at cell A5 for each sheet.

3. then Return the value of the first sheet range in A3 of the Master list, and the values of the next sheet one row below the last cell with a value in it,

if it helps, the column range is A:K only.

it's almost working, although i keep getting headers from the sheets in between the data is the only problem and can't find where i can specify exactly what cell to start at/how to reference it properly in VBA.

 

[jbeaucaire]

I presumed starting at row 2 was all that was needed to skip your titles:

Range("A2:K" & LR)

...edit that down to the correct starting row.

Every other sheet? That's a pretty vague way to process sheets. Is there something uniquely common about the sheetnames you want to process? Or something common about the sheets you want to skip?

 

[T-rev]

i suppose sheet names would be best in this situation...

names include..

"Energy"
"Materials"
"Industrials"
"Health Care"
"Consumer Discretionary"
"Consumer Staples"
"Financials"
"Information Technology"
"Telecommunications"
"Utilities"

not uniquely common.. although would it help maybe if i put a certain character in all of them e.g "1" at the end of each name?

 

[T-rev]

nothing uniquely common...

would putting a character after each one help e.g 1?

And is this correct for the data to go to A3 of the master sheet without deleting headers in A2?

For Each ws In Worksheets
        If ws.Name <> .Name And ws.Name <> "Market OverView" Then        'skip these sheets, use all others
            LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
            ws.Range("A5:K" & LR).Copy [COLOR=Red].Range("A" & .Rows.Count).End(xlUp).Offset(2)[/COLOR]
        End If

it looks right to me but it keeps deleting my headers, even when i change the offset... and i cannot change the Range in the Red part of the code..
(runtime error 1004, application or object defined error)

 

[jbeaucaire]

This:

.Range("A" & .Rows.Count).End(xlUp)

...means "look up column a and find the last used cell."

So if A20 was the last used cell, then it would find A20. We OFFSET down from there to paste into an empty cell, usually Offset(1). If you used Offset(2) you would get a blank row in between each pasted group.


If that list above is the name of the sheets you want to grab data from, you can use a "select case" approach to examine the name of each sheet and only run the commands on the sheets that match those names:

For Each ws In Worksheets
    Select Case ws.Name
        Case "Energy", "Materials", "Industrials", "Health Care", _
            "Consumer Discretionary", "Consumer Staples", "Financials", _
            "Information Technology", "Telecommunications", "Utilities"
            
            LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
            ws.Range("A5:K" & LR).Copy .Range("A" & .Rows.Count).End(xlUp).Offset(1)
        Case Else
            'these sheets would be skipped
    End Select
Next ws

 

[T-rev]

great all works, thank you.. although i am trying to paste it into A3 of the master sheet, it keeps pasteing into A2. how can i fix this?