We help IT Professionals succeed at work.

how to ref cell from open workbook

Hi,

I have a macro, that I want to reference a cell from an open workbook. How can i do this?

in the VBA, I want the value of L87 on "F&O Daily" to be the value of open workbook: "F&O Daily" A98 + ".xlsx" (A98 lists the workbook name that would be opened), B5 (on the A98 workbook).

i.e A98 on F&O Daily will say: "March 9 2015". The open workbook I want to ref would be March 9 2015.xlsx. I'd then want to use the data from B5 within this opened workbook. (on worksheet named: "Avg Daily Vol")

Thanks!

  With Sheets("F&O Daily")
'want to insert cell ref here
     End With

Open in new window

Comment
Watch Question

Top Expert 2015

Commented:
I'm not sure about the sheet name but you can do something like this to pick the value directly assuming that workbook is open...

range("L87").VALUE = WORKBOOKS(RANGE("A98").Value).Sheets("Your sheet name here").range("B5").VALUE

Open in new window


Saurabh...

Author

Commented:
tried, that having a bit of difficulty here..

Sub RollDates()
    With Sheets("F&O Daily")
     '.Range("M84").Value = .Range("M84").Value + 1
     '.Range("M87").Value = .Range("M87").Value + 1
     'MsgBox "Dates Rolled"
     Range("L87").Value = Workbooks(Range("A98").Value).Sheets("Avg Daily Vol").Range("B5").Value
     
     End With
End Sub("B5").VALUE

Open in new window


so I want L87.value = A98 (but need to add .xlsx) at the end of it. Then use sheet Avg Daily Vol B5 from that opened sheet.

I'm getting a syntax issue on the above.
Top Expert 2015

Commented:
I'm assuming the workbook name is mentioned on the F&O Daily sheet only on range A98 and you don't have to add .xlsx it picks up automatically in macro if that workbook is open...

Also if it doesn't work then what error you get??

Sub RollDates()
    With Sheets("F&O Daily")
     '.Range("M84").Value = .Range("M84").Value + 1
     '.Range("M87").Value = .Range("M87").Value + 1
     'MsgBox "Dates Rolled"
    .Range("L87").Value = Workbooks(.Range("A98").Value).Sheets("Avg Daily Vol").Range("B5").Value
     
     End With
End Sub

Open in new window


Saurabh...

Author

Commented:
subscript out of range.
run-time error 9
Top Expert 2016
Commented:
HI,

pls try

Sub RollDates()
    With Sheets("F&O Daily")
        .Range("L87").Value = _
            Workbooks(Format(.Range("A98"), "MMMM d yyyy") & ".xlsx").Sheets("Avg Daily Vol").Range("B5").Value
    End With
End Sub

Open in new window

Regards
Top Expert 2015

Commented:
Okay help me understand is the workbook name which is mentioned and

the value which you see by the code range("a98").value is it the same? or do you see any difference in that..you should see the value which is getting picked up in the code is it getting picked as number value or the date value...

You probably want to format it like this...

.Range("L87").Value = Workbooks(Format(.Range("A98").Value, "mmmm d yyyy")).Sheets("Avg Daily Vol").Range("B5").Value

Open in new window


You need to make sure the value name of the workbook is same as the name of your open workbook...

Author

Commented:
Rgonzo, you nailed it.

thanks!
Top Expert 2015

Commented:
jfrank you don't have to add .xlsx necessarily on it..if the workbook is open it will pick it up automatically..without the extension..

Author

Commented:
thank you , i appreciate all of your help!