跨多个工作表的VBA宏 [英] VBA Macro across multiple worksheets

问题描述

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

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:

1）选择工作表4中的某些单元格，复制到工作表4中的相邻单元格。

2）删除工作表3中单元格的范围。

3）工作表2中的CUT单元格范围然后将该范围的单元格粘贴到工作表3中。

4）从单独的工作簿中获取单元格范围并复制到工作表2.（我知道这是一个完全不同的问题，因为工作簿被自动发布，我将不得不找到一种链接两个。）

5）更新位于工作表4和工作表3中的枢轴表。

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.

我希望对此的前3个功能有所帮助。我已经粘贴了我现在的代码。

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

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 removing the select, the selection and the activesheet, you will be able to make this sheet-independent

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

这篇关于跨多个工作表的VBA宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！