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

 

[T-rev]

soo i have set a formatting macro to delete duplicates and blanks so i can offset by 2 and have it start at A3 of the master sheet, however it is still deleting anything in the cells A1:K2 (headers) whenever i run the macro...

 

[jbeaucaire]

This line is?

    .UsedRange.Offset(2).ClearContents  'remove data, leave titles in row 1:2

I don't see how it could be?

You could use more brute force:

    .Range("A3:A" & .Rows.Count).EntireRow.ClearContents  'remove data, leave titles in row 1:2

 

[jbeaucaire]

Thanks so much for your help, your truely a VBA master

i got it to stop deleting the headers,

if you would be so kind as to aid me in one final step...

i would like to copy the value of the sector name in A1 (which is a merged cell, so value only if possible)
into column I of the master sheet for each individual company.

sorry if this is getting rather tedious...

No problem but keep the discussion public unless discussing sensitive topics.

Copy A1 from where to where?

 

[jbeaucaire]

Anything else?

 

[T-rev]

no worries, thats it for this thread, thanks