Looping through worksheets in a single workbook while generating new workbooks

Question

I need to loop through 47 different worksheets in a single workbook.

The macro being run on each worksheet is creating a new individual workbook for that worksheet. My challenge is that once the worksheet from the original workbook is converted into a new workbook my macro now starts looping through the worksheet on the new workbook instead of the original. I have been trying to figure out some sort of code that counts each worksheet on the original workbook and then loops back to the original once the new workbook is created.

Sub PriceListWest()
'
' PriceListWest Macro
'

Dim Current As Worksheet

Windows("West price list master.xlsm").Activate 'Original workbook'

For Each Current In Worksheets

    ActiveSheet.Select 'Selecting worksheet in original workbook'
    ActiveSheet.Copy 'Copying worksheet in original workbook'

    'Challenge lies here now the loop goes through the new workbook versus returning to original workbook'

    Cells.Select 
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.EntireColumn.Hidden = False

Next

End Sub

Answer 1

It is a bad habit to use Select and Activate unless you keep track of exactly which sheet / cell is active from time to time. Refer to How to avoid using Select for tips on ways to avoid those commands.

Assuming your existing code successfully processed the first worksheet, when it started to process the second worksheet the ActiveSheet was still the recently created copy of the first worksheet. So that sheet would then be copied to another workbook, rather than copying one of the worksheets from your original workbook.

The following code uses a reference to the original workbook, and a reference to the Current object which your code already creates, to ensure that the correct sheets are being referenced:

Sub PriceListWest()
'
' PriceListWest Macro
'
    Dim wbMaster As Workbook
    Dim Current As Worksheet

    'Set a reference to the original workbook
    Set wbMaster = Workbooks("West price list master.xlsm")
    'Loop through each sheet in original workbook
    For Each Current In wbMaster.Worksheets
        'Copy the "current" sheet to a new workbook
        Current.Copy
        'The "Copy" command has now made the new workbook active, and
        ' "ActiveSheet" is the newly created sheet in that workbook

        With ActiveSheet
            'Copy entire sheet
            .Cells.Copy
            'Paste values
            .Cells.PasteSpecial Paste:=xlPasteValues, _
                                Operation:=xlNone, _
                                SkipBlanks:=False, _
                                Transpose:=False
            'Unhide all columns
            .Columns.Hidden = False
            'Set "active" cell to A1, just so the whole sheet isn't selected
            .Cells(1, 1).Select
        End With
    Next
    Application.CutCopyMode = False
End Sub

The code does continue to use ActiveSheet but only in a spot where we know that the active sheet is the newly copied sheet in the newly created workbook.

Answer 2

your main error lies on the fact that your Current variable holds the current worksheet while iterating through Worksheets collection, while ActiveSheetis the currently "Active" sheet and doesn't change until you Activate a new sheet (and looping doesn't Activate)

and after every ActiveSheet.Copy, the newly created workbook becomes the ActiveWorkbook and its only sheet the ActiveSheet

so you have to use Current instead of ActiveSheet

furthermore acting on all the Cells of a worksheet is time consuming and may rise memory issues: much better you refer to UsedRange property of Worksheet object

so you could code:

Option Explicit

Sub PriceListWest()
    '
    ' PriceListWest Macro
    '
    Dim Current As Worksheet

    For Each Current In Workbooks("West price list master").Worksheets
        Current.Copy '<--| copy current worksheet from 'original' workbook into a new workbook, this latter becomes the "active" workbook and it's only sheet the "Active" Sheet
        With ActiveSheet.UsedRange '<--| reference "Active" worksheet of current "Active" Workbook
            .value = .value
            .EntireColumn.Hidden = False
        End With
    Next
End Sub

Finally, acting like above will leave you with as many open workbooks as pasted sheets, while you may want to save and close them at every iteration, thus leaving you with the 'original' workbook only:

Option Explicit

Sub PriceListWest()
    '
    ' PriceListWest Macro
    '
    Dim Current As Worksheet

    For Each Current In Workbooks("West price list master").Worksheets
        Current.Copy '<--| copy current worksheet from 'original' workbook into a new workbook, this latter becomes the "active" workbook and it's only sheet the "Active" Sheet
        With ActiveSheet.UsedRange '<--| reference "Active" worksheet of current "Active" Workbook
            .value = .value
            .EntireColumn.Hidden = False
        End With
        ActiveWorkbook.SaveAs filepathandname '<-- save current workbook
        ActiveWorkbook.Close '<--| close it
    Next
End Sub