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
 

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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
 

skyraider83

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

firstly thank you for your time.

The function you gave me was great, but it wasnt really what I was looking for.

This link provides the pictorial representation of what I intend to do.
http://i405.photobucket.com/albums/pp135/skyraider83/a3.jpg

1) Enter 444 in the box
2) Activate macros
3) activate sheet "444" in the same workbook (i have 1000s of sheets)

Thank you once again!
 

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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:

skyraider83

New Member
Joined
Sep 28, 2008
Messages
3
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
 

seahawk56

New Member
Joined
Jun 23, 2008
Messages
30
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
 

Forum statistics

Threads
1,082,552
Messages
5,366,286
Members
400,882
Latest member
zaldy_a

Some videos you may like

This Week's Hot Topics

Top