Select a Tab based in cell contents

[husoi]

Hi everyone, I have a huge spreadsheet (4000 plus tabs I know I should behave myself ) this includes: The start tab where the users have a button that will allow searching for a reference (called project).

This will take him/her to the project front page.
This project front page Tab is named with the project reference and there are as many tabs as projects. Then there are another 5 tabs as sub pages for the same project “xxxxA1”, “xxxxA2”, “xxxxA3”…
The value xxxx is the project reference that is in cell A4 for user information.

My question is: How can I create a macro that will look into cell “A4” add the “A1” (one button for each “Ax” and navigate to that tab. Thank you for the help.

 

[Michael M]

Can you give us a SMALL example ?
What is the project tab name for example ? and how does the user type in the search ?

This bit has me a bit confused !!

How can I create a macro that will look into cell “A4” add the “A1”

 

[husoi]

It's confusing to me too

Cell A4 will contain something like: "422154W" which is the project's reference then I will have 5 other sheets called "422154WA1" to "422154WA5".
Project 422154W[TABLE="width: 20"]
<tbody>[TR]
[TD]Project Tab
[/TD]
[TD]project sub tab
[/TD]
[/TR]
[TR]
[TD]422154W
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]422154WA1
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]422154WA2
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]422154WA3
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]....
[/TD]
[/TR]
</tbody>[/TABLE]
The idea is to allow users input the project reference in an input box on start sheet (This bit is sorted and working fine) and then from there go to each sub tab with the press of a button.
Because there are 1000's of different projects and new ones added every day I need to have a macro instead of creating hyperlinks for each button on every project.

Not sure if this makes it clearer...
BTW, thank you for the reply

 

[hippiehacker]

basically something like this

when the button is on the Project Sheets.

Sub button_click_A1()
Sheets(ActiveSheet.Name & "A1").Activate
End Sub

 

[Michael M]

What does the user input into the InputBox
Maybe you could post that code and we'll modify into it !

 

[husoi]

basically something like this

when the button is on the Project Sheets.

Sub button_click_A1()
Sheets(ActiveSheet.Name & "A1").Activate
End Sub

YYYYYEEEEEYYYYY
THANK YOU, THANK YOU, THANK YOU.
So easy when you know what you're doing. lol
Works a treat.

 

[Michael M]

Glad you got it sorted....@hippiehacker, I couldn't see the forest for all those trees...

 

[hippiehacker]

What does the user input into the InputBox
Maybe you could post that code and we'll modify into it !

as far as I understood the 'landing' sheets(Startsheet) the user puts in the Project number and you are navigated to the project main sheet on this project main theet you have a button for each project sub sheets to navigate to them.

 

[husoi]

What does the user input into the InputBox
Maybe you could post that code and we'll modify into it !

Thank you for the trouble.
I'm using this macro for the first bit:

Sub macrotab()

Dim project As String

project = InputBox(Prompt:="Which project you wish to see?", Title:="Project reference")

If project <> vbNullString Then

On Error Resume Next
Sheets(project).Activate

If Err.Number <> 0 Then MsgBox "Wrong reference: " & project
On Error GoTo 0
End If

The user input project reference here, the project reference is a series of number and letters as in the example I posted.
But the solution given by Hippiehacker works perfectly.