VBA code needs a bit of tweaking

[danjuma]

Need a bit of help please guys. Got the following VBA code in sheet 1. which works fine for that workbook. However, when I have two or more duplicate workbooks opened, the code does not work as it should (affected by the duplicate workbooks). The way I have got around this is to open each duplicate workbook in separate instances of excel. How can I get the code to be workbook specific, so that I would be able to open duplicate workbooks in the one instance of excel.

Many thanks
Dan

Dim refreshCount As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
If Range("y1").Value = 1 Then
refreshCount = refreshCount + 1
Else
refreshCount = 0
End If
If refreshCount > 5 Then
Range("y2").Value = 1
Else
Range("y2").Value = 0
End If
If Cells(2, 5) = "In Play" Then
If Cells(1, 27) = "" Then Cells(1, 27) = Cells(2, 3)
If Cells(1, 27) <> "" Then Cells(1, 28) = DateDiff("s", Cells(1, 27), Cells(2, 3))
End If
If Cells(2, 5) <> "In Play" And Cells(2, 6) <> "Suspended" Then
Cells(1, 27) = ""
Cells(1, 28) = ""
End If
Application.EnableEvents = True
End Sub

PS. Not that versed in VBA, the above code was just a mix & match of other people's codes.

[osknows]

One way is to replace all references to Range and Cell objects with the following

ThisWorkbook.Sheets(Target.Worksheet.Name).Range("y1").Value

and

ThisWorkbook.Sheets(Target.Worksheet.Name).Cells(2, 5)


'ThisWorkbook' assumes the workbook the code is running in and 'Target.Worksheet.Name' picks up the name of the worksheet the event was fired from

[danjuma]

Thanks osknows, will try your suggestion. Cheers

[danjuma]

Works fine osknows. Many thanks!

[mak]

One way is to replace all references to Range and Cell objects with the following

ThisWorkbook.Sheets(Target.Worksheet.Name).Range("y1").Value

and

ThisWorkbook.Sheets(Target.Worksheet.Name).Cells(2, 5)


'ThisWorkbook' assumes the workbook the code is running in and 'Target.Worksheet.Name' picks up the name of the worksheet the event was fired from

Can someone clarify, if i have to replace "ThisWorkbook" with the actual name of workbook or to keep it as it is (the same for worksheet?)

[alrodopial]

Can someone clarify, if i have to replace "ThisWorkbook" with the actual name of workbook or to keep it as it is (the same for worksheet?)

You don't have to replace it.

[mak]

Can someone clarify, if i have to replace "ThisWorkbook" with the actual name of workbook or to keep it as it is (the same for worksheet?)

You don't have to replace it.

thanks al