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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

jfrank85Author Commented:
subscript out of range.
run-time error 9
0
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.