Copy from different sheets to summary sheet

[NoCanDo]

Hi all

I have a problem with a macro script I need to write (unsure where to start probably).

I’m using Excel 2003.

In my spreadsheet I have many worksheets (90+) and I need to copy information from specific cells (the same for each sheet, but the cell range varies each month) from any identified worksheet in the spreadsheet (the specific sheets I want to copy from will differ from month to month but are given in a list.). Then paste this data in sequential order of identified worksheet but transposed into sheet Summary.

In effect this is:

1. Identify the sheet name from the ListingOrder in worksheet LISTING
2. Identify the monthly range of cells
3. Copy the data (Indicator, Month, Name and Activity)
4. Go to sheet SUMMARY
5. Go to next available cell in column A
6. Paste special, transpose
7. Loop to next sheet name in the ListingOrder
8. Undertake actions 1-7 until all relevant sheets have been copied from and pasted to Summary sheet.

The end result is as shown in the Worksheet SummaryExample – no need to worry about formatting.

Samole data and SummaryExample in attached spreadsheet.

Sorry I can’t offer any script at present – I’ve been trying to work it out using Vlookups etc but get the feeling it would be easier via a macro.

Hope someone with more ability than I have can help me out.

Kind regards