Consolidate feature using macro with differing numbers of sheets

[kariw]

I'm using a macro to select all sheets in a workbook and am then using the Consolidate feature under the Data header to consolidate the data into a single sheet. The number of sheets varies though (could be 3 one day and 10 the next). Is there a way to select all sheets, regardless of the names of the sheets and regardless of the number of sheets in the workbook, and utilize the Consolidate feature? The code below is what I got when I recorded the macro (I don't need that specific range - I just selected a large enough range so I know all the data would be picked up)


screenshotmacro.png

[Bernie Deitrick]

This assumes that you have a blank first sheet in the workbook with the macro, and that File.xlsx is open at the time. You can change any of those assumptions easily...

Please Login or Register  to view this content.

[kariw]

That seems to have worked perfectly! Thank you so much!