retrieve values from same column across multiple worksheets and put on one master worksheet

[westend9876]

Hi I have 52 worksheets in my workbook. Let's just say 'Sheet 1', 'Sheet 2', ...'Sheet 52'. Each worksheet has values in some, but not all cells in column AR. I want to extract the data in all cells in column AR across the various worksheets and place them all on 'Master-Sheet' in column A. Exclude any cells with no data. Also, I'd like to do this without VBA. Thanks.

 

[Nebu]

Can you please upload a workbook with some dummy data...

 

[NARAYANK991]

Hi ,

Something which can be done so easily using VBA will become a formula nightmare if you implement it using formulae.

Narayan

 

[westend9876]

Hi ,

Something which can be done so easily using VBA will become a formula nightmare if you implement it using formulae.

Narayan

I'm sure you're right. I just have never used VBA. But, I'm familiar with formulas. I'm willing to give VBA a shot though. Please advise. Thanks.

 

[NARAYANK991]

Hi ,

Shall I code using the same nomenclature you have mentioned viz. Sheet1 , Sheet2 ,... , column AR , and column A ?

What should be the data range ? Can I assume row 2 through row 1000 ? Or should I detect the last row of data on each sheet ?

Narayan

 

[westend9876]

No. The same nomenclature used in the example will not work. The sheets are actually named after currency pairs. Can the code read from a list of the sheet names a master list? Also, the data range should be from a pre-determined range variable as it will change regularly. For, example I will need range AR125:AR300 today and the next I may need AR200:AR275.

 

[NARAYANK991]

Hi ,

See this file and comment.

Narayan