VBA Macro across multiple worksheets

Question posted 2012 · +7 upvotes

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) 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.

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.