在生成新工作簿的同时循环浏览单个工作簿中的工作表 [英] Looping through worksheets in a single workbook while generating new workbooks

问题描述

我需要在一个工作簿中循环浏览47个不同的工作表.

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

推荐答案

使用 Select 和 Activate 是一个坏习惯，除非您准确地了解哪个工作表/单元不时处于活动状态.请参阅如何避免使用选择" 以获取避免这些命令的方法的提示.

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.

假设您现有的代码成功处理了第一个工作表，则当它开始处理第二个工作表时， ActiveSheet 仍然是第一个工作表的最近创建的副本.这样一来，该工作表将被复制到另一工作簿，而不是从原始工作簿中复制其中一个工作表.

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.

以下代码使用对原始工作簿的引用以及对您的代码已经创建的 Current 对象的引用，以确保引用了正确的工作表:

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

代码确实继续使用 ActiveSheet ，但仅在我们知道该活动工作表是该工作表中新复制的工作表的地方新创建的工作簿.

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.

这篇关于在生成新工作簿的同时循环浏览单个工作簿中的工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！