Multiple Macros

[Shaun]

I am still having some issues running the same macro on different workbooks i have tried different ways so they will not upset each other, my sheets always have the same sheet names, just wondering if having different names would help, here is an example.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Static MyMarket As Variant
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
With ThisWorkbook
If Sheets("Data").Range("T2").Value <> 1 Then
Sheets("Data").Range("T3").Value = 0
GoTo Mrkt
End If
If Sheets("Data").Range("T2").Value = 1 And Sheets("Data").Range("T3").Value <> 1 Then
Sheets("Control").Range("Z17").Value = "Bet Placed"

Application.CutCopyMode = False
Sheets("Data").Range("T3").Value = 1
End If
Mrkt:
    If Sheets("Data").Range("A1").Value = MyMarket Then
        GoTo Xit
    Else
    MyMarket = Sheets("Data").Range("A1").Value
    Sheets("Control").Range("Z18").Value = "Macro Armed"
    Application.OnTime Now + TimeValue("00:00:08"), "Test"
    End If

End With
Xit:
Application.EnableEvents = True
End Sub


[alrodopial]

What's the problem at the code you provided?

[Shaun]

If i am running multiple workbooks and i am on 1 workbook when the macro fires it causes an error.

[GaryRussell]

When you use "With ThisWorkbook" you need to prefix "Sheets" with "." so that it prefixes with ThisWorkbook. Your code should read as follows.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Static MyMarket As Variant
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
With ThisWorkbook
If .Sheets("Data").Range("T2").Value <> 1 Then
.Sheets("Data").Range("T3").Value = 0
GoTo Mrkt
End If
If .Sheets("Data").Range("T2").Value = 1 And .Sheets("Data").Range("T3").Value <> 1 Then
.Sheets("Control").Range("Z17").Value = "Bet Placed"

Application.CutCopyMode = False
.Sheets("Data").Range("T3").Value = 1
End If
Mrkt:
    If .Sheets("Data").Range("A1").Value = MyMarket Then
        GoTo Xit
    Else
    MyMarket = .Sheets("Data").Range("A1").Value
    .Sheets("Control").Range("Z18").Value = "Macro Armed"
    Application.OnTime Now + TimeValue("00:00:08"), "Test"
    End If

End With
Xit:
Application.EnableEvents = True
End Sub

[Shaun]

Ok np, should i do that with all my macros that run on each sheet as they are copies of each other?

[GaryRussell]

Yes. You were right to use "With ThisWorkbook". This prevents the workbooks interfering with each other, but it's useless without that "." prefix because it's just not being used.

[Shaun]

Hi, i made some changes along the lines you explained but i have one thing i need to check.

In my code at one point i past some data to another workbook and i am wondering if the way i have written it is correct with out the "." the section in red is where it will copy code to another workbook.

Code: Select all

[Private Sub Worksheet_Change(ByVal Target As Range)
Static MyMarket As Variant
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
With ThisWorkbook
If .Sheets("DataWin").Range("T2").Value <> 1 Then
   .Sheets("DataWin").Range("T3").Value = 0
GoTo Mrkt
End If
If .Sheets("DataWin").Range("T2").Value = 1 And

.Sheets("DataWin").Range("T3").Value <> 1 Then
.Sheets("Control").Range("AC25").Select
Selection.Copy
Windows("Race Upload").Activate
Sheets("Race").Select
Range("I7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Doubles Lay Betting.xlsm").Activate

Code: Select all

.Sheets("Control").Range("AU13").Value = Sheets("Control").Range("AT13").Value
   .Sheets("Control").Range("AK22:AK30").Value = Sheets("Control").Range("AJ22:AJ30").Value
    Shell """C:\Users\Shaun\Desktop\Doubles Lay Betting\NSW Upload.exe""", vbNormalFocus
   .Sheets("DataWin").Range("T3").Value = 1
End If
Mrkt:
    If .Sheets("DataWin").Range("A1").Value = MyMarket Then
        GoTo Xit
    Else
        MyMarket = .Sheets("DataWin").Range("A1").Value
       .Sheets("Control").Range("AC17").Value = Sheets("Control").Range("AC15").Value
    If .Sheets("Control").Range("R6").Value = 1 Then
       .Sheets("Control").Range("AC27").Value = Sheets("Control").Range("AC25").Value
    FirstLeg
    Secondleg
    Rdouble
    RaceResults
    Else
    End If
End If
End With
Xit:
Application.EnableEvents = True
End Sub



[osknows]

The '.' always refers back to the previous With statement be it a range,sheet, or other object. So you have to be careful if you're reading and writing to separate objects within only one With statement.

It may be easier to define your objects upfront which will make it easier to handle. Also I would avoid all use of Select and Activate. Your code up to the red could be improved using something like

Code: Select all

Dim objCurrentWB As Workbook
Dim objWriteWB As Workbook
Dim booObjectsLoaded As Boolean


Private Sub Worksheet_Change(ByVal Target As Range)
Static MyMarket As Variant

If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False

If booObjectsLoaded = False Then
    'set Objects - In this case a read and write workbooks
    Set objCurrentWB = ThisWorkbook
    Set objWriteWB = GetObject("C:\Users\osknows\Desktop\Race Upload.xlsm")
    'objWriteWB.Parent.Windows(objWriteWB.Name).Visible = True 'set
visible or not
    booObjectsLoaded = true
End If

With objCurrentWB
    If .Sheets("DataWin").Range("T2").Value <> 1 Then
       .Sheets("DataWin").Range("T3").Value = 0
    'GoTo Mrkt
    End If
       
    If .Sheets("DataWin").Range("T2").Value = 1 And _
    .Sheets("DataWin").Range("T3").Value <> 1 Then
   
        objWriteWB.Sheets("Race").Range("I7").Value = .Sheets("Control").Range("AC25").Value
       
    End If
'.
'.
'etc
End With
Application.EnableEvents = True
End Sub


[Shaun]

Thanks for the advice, my excel is more limited than i would like to admit i think i need an excel for dummies book, google is a big help it times.