Newbie vba - How do I reference cells in another workbook
DIM sWBook as sring
DIM WB as Workbook
DIM WS as Worksheet
DIM sFormula as String
set WS = Activesheet
sWBook = WS.Range("B4").Value
SET WB = Workbooks.Open(sWBook)
sFormula = = "=max([" & sWBook & "]sheet1!$b:$b)"
WS.Range("A7").Offset1,1).Formula = sFormula
'this line converts the formula to values
WS.Range("A7").Offset1,1).value = _
WS.Range("A7").Offset1,1).value
WB.Cloase False
HTH
Patrick Molloy
Microsoft Excel MVP
-----Original Message-----
Data is transferred from another program and each
transfer opens a new
workbook in Excel. I have a workbook (Main) that
processes the data. I have
the workbook name in cell B4 say Book5 and part of the
macro looks like this
Dim wbook
Range("b4").Select
wbook = ActiveCell.Offset(0, 0).Value
Workbooks(wbook).Activate
'Processes some data
Workbooks("Main").Activate
Range("a7").Select
ActiveCell.Offset(1, 1).Value = "=max([book5]sheet1!
$b:$b)"
My question is how do I write the last line using the
variable wbook
instead of the workbook name book5
Thanks
Ian
.
|