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

jfrank85Asked:
Who is Participating?
 
Rgonzo1971Commented:
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
0
 
Saurabh Singh TeotiaCommented:
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...
0
 
jfrank85Author 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.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Saurabh Singh TeotiaCommented:
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...
0
 
jfrank85Author Commented:
subscript out of range.
run-time error 9
0
 
Saurabh Singh TeotiaCommented:
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...
0
 
jfrank85Author Commented:
Rgonzo, you nailed it.

thanks!
0
 
Saurabh Singh TeotiaCommented:
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..
0
 
jfrank85Author Commented:
thank you , i appreciate all of your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.