跨多个工作表的 VBA 宏

VBA Macro across multiple worksheets

提问by kmiao91

I am trying to run a single macro which performs functions on multiple worksheets. Let's say I have assigned the macro button on worksheet 4. I have listed the functions I want it to perform step by step:

我正在尝试运行一个在多个工作表上执行功能的宏。假设我已经在工作表 4 上分配了宏按钮。我已经列出了我希望它逐步执行的功能：

1) Select certain cells in worksheet 4 and copy to adjacent cells in worksheet 4.
2) delete range of cells in worksheet 3.
3) CUT range of cells in worksheet 2 then paste this range of cells into worksheet 3.
4) Take range of cells from a separate workbook and copy into worksheet 2. (I know this is an entirely different problem as the workbook is automatically published and I will have to find a way to link the two.)
5) Update pivot tables located within Worksheet 4 and Worksheet 3.

1) 选择工作表 4 中的某些单元格并复制到工作表 4 中的相邻单元格
。2) 删除工作表
3 中的单元格范围。3) 剪切工作表 2 中的单元格范围，然后将此单元格范围粘贴到工作表 3 中
。4) 取范围一个单独的工作簿中的单元格并复制到工作表 2 中。（我知道这是一个完全不同的问题，因为工作簿是自动发布的，我必须找到一种方法来链接两者。）
5）更新位于工作表 4 中的数据透视表和工作表 3。

I would love help on the first 3 functions of this. I've pasted my current code below.

我很想帮助它的前 3 个功能。我在下面粘贴了我当前的代码。

Sub START()

Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim sh4 As Worksheet

Set sh1 = ActiveWorkbook.Sheets("Brand")
Set sh2 = ActiveWorkbook.Sheets("CurrentWeek")
Set sh3 = ActiveWorkbook.Sheets("PriorWeek")
Set sh4 = ActiveWorkbook.Sheets("Pivot")

sh4.Range("B29:B30").Select
Selection.Copy

sh4.Range("C29").Select
ActiveSheet.Paste

sh3.Range("A4:AC1000").Select
Selection.Delete

sh2.Range("A4:AC1000").Select
Selection.Copy

sh3.Range("A4").Select
ActiveSheet.Paste

End Sub

It works... but it only works when I'm in the right worksheet to perform a specific function.

它有效......但只有当我在正确的工作表中执行特定功能时才有效。

回答by nutsch

By removing the select, the selectionand the activesheet, you will be able to make this sheet-independent

通过删除select、selection和activesheet，您将能够使此工作表独立

Sub START()

Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim sh4 As Worksheet

Set sh1 = ActiveWorkbook.Sheets("Brand")
Set sh2 = ActiveWorkbook.Sheets("CurrentWeek")
Set sh3 = ActiveWorkbook.Sheets("PriorWeek")
Set sh4 = ActiveWorkbook.Sheets("Pivot")

sh4.Range("B29:B30").Copy sh4.Range("C29")

sh3.Range("A4:AC1000").Delete

sh2.Range("A4:AC1000").Copy sh3.Range("A4")

End Sub

回答by Scott Holtzman

You are off to a great start. Just little more refinement and you'll have it.

你有一个很好的开始。只需稍加改进，您就会拥有它。

Basically, there's no need to .Selectyour ranges (sheets, workbooks, etc), at least in this case. You can work directly with them and by using the Copysupply the destination where they will be copied.

基本上，.Select至少在这种情况下，不需要您的范围（工作表、工作簿等）。您可以直接与他们合作，并通过使用Copy供应他们将被复制的目的地。

See code below:

见下面的代码：

Sub START()

Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim sh4 As Worksheet
Dim wkb As Workbook

Set wkb = Workbooks("wkbName") '-> best to call workbooks by name, as opposed to "ActiveWorkbook", also best to set it to object

With wkb '-> now we can work with this object directly and succinctly

    Set sh1 = .Sheets("Brand")
    Set sh2 = .Sheets("CurrentWeek")
    Set sh3 = .Sheets("PriorWeek")
    Set sh4 = .Sheets("Pivot")

    sh4.Range("B29:B30").Copy sh4.Range("C29")

    'sh3.Range("A4:AC1000").Delete -> you don't need this if you are overwritting it

    sh2.Range("A4:AC1000").Copy sh3.Range("A4")

End With

End Sub

回答by jstiene

sheets("name1").range("B29:B30").copy Destination:=sheets("name2").range("C29")

sheet("name1").range("B29:B30").copy Destination:=sheets("name2").range("C29")

Will copy from one to another sheet assuming the sheet names are name1 and name2

假设工作表名称为 name1 和 name2，将从一个工作表复制到另一个工作表

回答by steven

Sub START()

Sheet("Pivot").Range("B29:B30").Copy Sheet("Pivot").Range("C29")
Sheet("CurrentWeek").Range("A4:AC1000").Copy Sheet("PriorWeek").Range("A4")

End Sub