CODEWITHSUNDEEP
excelvba
momodude22
momodude22

Reputation: 112

Run Macro in specific Workbook only

I have two workbooks that for data exchange. Workbook A pulls info from Workbook B.

I have macros running in A that to be performed in A.

When I open A before B, the macros are being performed in B. If I open B before A then the macros work in A as expected.

Private Sub Workbook_Open()
'If ThisWorkbook.Name = "A" Then
    ThisWorkbook.Sheets("Background").Select
    Call Background_Lists
    Call Find_Missing
'End If
End Sub

I attempted to use sheet/workbook activation as well as toying with an If statement (commented out).

Edit: Here is code to Background_Lists

Sub Background_Lists()
a = 0
Range("E4:E2004").Clear
Range("B4:B2004").Value = Range("=Parts!B18:B2018").Value
Range("D4:D2004").Value = Range("=[B.xlsx]Sheet1!A2:A2002").Value
For i = 4 To 2004
    If Cells(i, 4).Value >= 300000 Then
        Cells(4 + a, 5).Value = Cells(i, 4).Value
        a = a + 1
    End If
Next i
End Sub

Upvotes: 0

Views: 6077

Answers (2)

VBA Pete
VBA Pete

Reputation: 2666

Try to define each workbook e.g.:

Private Sub Background_Lists()
Dim WorkbookA As Workbook
Dim WorkbookB As Workbook
Dim WorkSheetA as Worksheet
Dim WorkSheetB as Worksheet
Dim WorkSheetParts as Worksheet

Set WorkbookA = Workbooks("PATIENT_TRACK.xlsm")
Set WorkbookB = Workbooks("PATIENT_DATA.xlsx")
Set WorkSheetA =WorkbookA.Worksheets("Background") 
Set WorkSheetB =WorkbookB.Worksheets("Sheet1") 
Set WorkSheetParts =WorkbookA.Worksheets("Parts") 


a = 0
WorkSheetA.Range("E4:E2004").Clear
WorkSheetA.Range("B4:B2004").Value = WorkSheetParts .Range("B18:B2018").Value
WorkbookA.Range("D4:D2004").Value = WorkSheetB.Range("A2:A2002").Value
For i = 4 To 2004

If WorkSheetA.Cells(i, 4).Value >= 300000 Then
    WorkSheetA.Cells(4 + a, 5).Value = WorkSheetA.Cells(i, 4).Value
    a = a + 1
End If
Next i
End Sub

Now you can you just use WorkbookA or WorkbookB instead of ThisWorkbook and point the macro to the correct workbook. Let me know if this works for you.

Upvotes: 1

Ralph
Ralph

Reputation: 9434

Adjust the sub Background_Lists to the following:

Sub Background_Lists()

a = 0
With ThisWorkbook.Sheets("Background")
    .Range("E4:E2004").Clear
    .Range("B4:B2004").Value = .Range("=Parts!B18:B2018").Value
    .Range("D4:D2004").Value = .Range("=[B.xlsx]Sheet1!A2:A2002").Value
    For i = 4 To 2004
        If .Cells(i, 4).Value >= 300000 Then
            .Cells(4 + a, 5).Value = .Cells(i, 4).Value
            a = a + 1
        End If
    Next i
End With

End Sub

Upvotes: 0

Related Questions