CODEWITHSUNDEEP
vbaexcelmacros
Jerremy Leedham
Jerremy Leedham

Reputation: 97

Make macro run in specific workbook

This seems like such a simple request, but I can't seem to find any answers online. I have two open workbooks (lets say A and B). All I want to do is run a macro that I have created in Workbook B and run it (by click a shape that I've assigned a macro to) through Workbook A, but the macro running in Workbook B

The macro I created for Workbook B is...

Sub HistoricalDataShift()
    Dim ws As Worksheet
    For Each ws In Sheets
        ws.Activate'
        Rows("18:1000").Select
        Selection.Copy
        Range("A19").Select
        ActiveSheet.Paste
        Rows("15:15").Select
        Selection.Copy
        Range("A18").Select
        ActiveSheet.Paste
    Next ws
End Sub

Then I created a second macro in Workbook B that has...

Sub ApplicationRun()
    Application.Run ("WorkbookB.xlsm!HistoricalDataShift")
End Sub

But each time I try the macro keeps running in Workbook A.

If I could get a helping hand that would be appreciated.

Upvotes: 2

Views: 5693

Answers (4)

D_Bester
D_Bester

Reputation: 5931

All you need to do is rewrite HistoricalDataShift to operate on itself. It should work just fine then.

Sub HistoricalDataShift()
    Dim wb As Workbook
    Set wb = ThisWorkbook
    Dim ws As Worksheet
    For Each ws In wb.Worksheets
        ws.Activate '
        ws.Rows("18:1000").Select
        Selection.Copy
        ws.Range("A19").Select
        ActiveSheet.Paste
        ws.Rows("15:15").Select
        Selection.Copy
        ws.Range("A18").Select
        ActiveSheet.Paste
    Next ws
End Sub

Also to make your code work better, you can do this:

Sub HistoricalDataShift()
    Dim wb As Workbook
    Set wb = ThisWorkbook
    wb.Activate
    Dim ws As Worksheet
    For Each ws In wb.Worksheets
        Call ws.Rows("18:1000").Copy(ws.Range("A19"))
        Call ws.Rows("15:15").Copy(ws.Range("A18"))
    Next ws
End Sub

Upvotes: 1

MikeT
MikeT

Reputation: 57063

You can use Worksheets("<worksheetname>")

e.g. Worksheets("A").Activate

or cv = Worksheets(Worksheet).Cells(DataSeriesEnd, rc_index)

where Worksheet holds the sheet name.

etc.

This snippet will go through the entire collection of worksheets, where w is the current worksheet name :-

For Each w In Worksheets
    .......
Next w

Upvotes: 1

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96781

In this short example, we assume that WorkbookB.xlsm is initially the only open workbook and hosts this macro:

Sub HistoricalDataShift()
    Dim wkbB As Workbook
    Dim wkbA As Workbook

    Set wkbB = ThisWorkbook
    Workbooks.Open Filename:="WorkbookA"
    Set wkbA = ActiveWorkbook
    wkbA.Sheets(1).Range("B9").Value = "whatever"
End Sub

Upvotes: 1

grug.0
grug.0

Reputation: 355

Try declaring your workbook object?

Dim wkbkA as workbook
set wkbkA = 'directory here

then run your code in a With... End With

With wkbkA
    .range('etc.........

End With

Upvotes: 1

Related Questions