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

[skyraider83]

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]

Hi
if A1 of sheet1 has a sheetname(123)
B1 of sheet2 = indirect(A1 & "!B12) will pull B12 of sheet 123.
in VBA

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]

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]

Hi
right click on the "Main" tab and choose "View code" and paste the following codes into the right blank area.

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

 

[skyraider83]

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]

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