Linking workbooks

[Pat]

I use the following code to run within a workbook.

Private Sub Print_Click()
' retail sales
Calculate
Selection.AutoFilter Field:=10
Selection.AutoFilter Field:=11
Dim sProd As String
Dim icol As Long
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range, Target As Range
Dim res As Variant
Set rng = Worksheets("ControlCentre").Range("BH30")
res = Application.Match(ActiveSheet. _
Range("W2").Value, rng, 0)
If Not IsError(res) Then
icol = rng(res).Column
Else
MsgBox "Retail sale not matched"
Exit Sub
End If
With ActiveSheet
Set rng1 = Nothing
On Error Resume Next
Set rng1 = .Range("L24:L800"). _
SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0
If rng1 Is Nothing Then
MsgBox "No Quantities in Retail sale"
Exit Sub
End If
End With

For Each Target In rng1
sProd = Target.Parent.Cells(Target.Row, 6)

res = Application.Match(sProd, _
Worksheets("ControlCentre").Range("C77:C1000"), 0)
If Not IsError(res) Then
With Worksheets("ControlCentre")
If IsNumeric(Target.Value) Then
Set rng2 = .Cells(res + 76, icol)
rng2.Value = rng2.Value + Target.Value
End If
End With
Else
MsgBox "Product Not found: " & sProd
End If
Next

Selection.AutoFilter Field:=12, Criteria1:="<>"
'Selection.AutoFilter Field:=11

Worksheets("ControlCentre").Calculate
Calculate

End Sub

I have thus moved the sheet to a new workbook and kept the link. What I want
to do is run the code from the new workbook to the old workbook. The code
stops at the line:

Set rng = Worksheets("ControlCentre").Range("BH30")

Anyone familar with all of this?
Pat

 

[Dick Kusleika]

Pat

I have thus moved the sheet to a new workbook and kept the link. What
I want to do is run the code from the new workbook to the old
workbook. The code stops at the line:

Set rng = Worksheets("ControlCentre").Range("BH30")

If you use Worksheets without qualifying it with a Workbook object, it will
assume you mean the activeworkbook (depending on where the code is). If
you're getting a subscript out of range error, it means that there is no
worksheet called ControlCentre in that workbook. To fix, qualify with a
specific workbook

Set rng =
Workbooks("Otherbook.xls").Worksheets("ControlCentre").Range("BH30")

 

[Pat]

Thank you for that and also the link to your homepage.

Cheers