Calling out a sheet from another workbook based on a cell value

skyraider83

New Member
Joined
Sep 28, 2008
Messages
3
Hi All,

I have this problem to get over. I need to call out a sheet based on the same name as a cell value which I would input. Basically it's for easier extraction of a particular worksheet from another workbook.

For eg: Each sheet has a unique number associated to it (eg: 123). i want to call out that sheet by typing in "123" into a particular cell, then activating the macros to extract that particular sheet named "123".

Pls advise! Have been stuck with it for days!

Thanks a lot
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Hi
if A1 of sheet1 has a sheetname(123)
B1 of sheet2 = indirect(A1 & "!B12) will pull B12 of sheet 123.
in VBA
Code:
Dim b As String, a as string
b = Cells(1, 1)
a = Worksheets(b).Cells(12, 2)
MsgBox a
return the same value.I have given just an example. the syntax depends on the context.
ravi
 
Upvote 0
Hi
right click on the "Main" tab and choose "View code" and paste the following codes into the right blank area.
Code:
Private Sub worksheet_change(ByVal target As Range)
If target.Address = "$E$8" Then
Worksheets(target.Value).Activate
End If
End Sub
Save the workbook.Now you enter the sheetname in cell E8 and it will take you there
Ravi
 
Last edited:
Upvote 0
Hi Ravi,

Am getting there! However, an error resulted, stating that "Argument not optional (Error 449)"

I don't really understand the help description.

Sorry for the lack of understanding

Regards,
Damien
 
Upvote 0
Hi skyraider83,
I'm trying to do something very similar and I'm getting the same error message. Were you able to figure it out?
Thanks,
Seahawk56
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top