VBA Macro across multiple worksheets

10
votes

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.

4
vbaexcelexcel-2007
Instead of unreadable variable names such as sh1, sh2 etc. why not call them something useful and transparent like shtBrand, shtCurrentWeek etc. One guy at work does this exact same sh1 sh2 business and it drives everyone nuts when we try to read his code. – Jean-François Corbett
@Jean-FrançoisCorbett Yes, that's actually a good idea. The reasoning I didn't do this at first is because in my business, no one knows how to code. This task i'm trying to automate has already been manually done for about 2-3 years. It's absurd, but that is a great idea! Thank you. – kmiao91

4 Answers

15
votes

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
5
votes

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

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

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
0
votes

sheets("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

-2
votes
Sub START()

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

End Sub