Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking workbooks
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking workbooks
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("ControlCent re").Range("BH30") -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking workbooks
Thank you for that and also the link to your homepage.
Cheers "Dick Kusleika" wrote in message ... 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("ControlCent re").Range("BH30") -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking workbooks | Excel Discussion (Misc queries) | |||
linking many workbooks | Excel Discussion (Misc queries) | |||
linking workbooks | Excel Worksheet Functions | |||
linking to other workbooks | Excel Discussion (Misc queries) | |||
linking workbooks | Excel Discussion (Misc queries) |