VBA to copy worksheets based on index # and add subtotals at the bottom

Hi, I have two separate questions. I have a VBA that basically copies and pastes values from a master excel file to a new workbook. The first question is basically how to copy these worksheets based on the position and not the worksheet name, since Array SheetNames1 can change every month.

The other issue is that at the end of two of the worksheets named Forecast vs. Orders and KP-Machine, there is a subtotal row (SUBTOTAL(9,...)) that I do not want pasted as values, and the data range will change every day. Is there a code that can look through column B and find the word "SUBTOTAL", and calculate the formula in that row through columns S:BA?

Below is my starting vba code:

Please Login or Register  to view this content.

Thanks Bernie!!! This worked great for our workbook!