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

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


.

Ian, Suggest a few changes to your code, as follows plus the one way of
referencing another workbook using the formula.

Dim wbook As String

wbook = Range("B4").Value & ".xls"
Workbooks(wbook).Activate

'Processes some data

Workbooks("Main.xls").Activate
Range("A7").Formula = "=max([" & wbook & "]sheet1!$B:$B)"

Cheers
Nigel

"Ian" wrote in 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

Thanks Patrick and Nigel for fixing the problem

Regards

Ian
"Ian" wrote in 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