Select Differing Number of Sheets Dynamically

[kariw]

I'm using a macro to select all sheets in a workbook and apply formatting. The number of sheets varies though (could be 3 one day and 10 the next). As long as there are 6 sheets, the code below works. How could I change this to select all sheets, regardless of how many sheets are in the workbook?


screenshotmacro.png

Along the same lines, I select all sheets and consolidate the data into a single sheet in a different file. I'm not sure how to do this in a way that allows me to consolidate the data from all sheets, regardless of the names of the sheets and regardless of the number of sheets in the workbook.

screenshotmacro.png

[Rick Rothstein]

To answer your first question, this single line of code will select all of the sheets in the active workbook...

Sheets(Evaluate("TRANSPOSE(ROW(1:" & Sheets.Count & "))")).Select

[kariw]

Thank you! That worked perfectly!