Code for manually select a tab

[husoi]

hi all,

I've created a VBA code that imports part of a workbook into a reporting spreadsheet. this works really well until users decide to change the sheets.
The code is quite extensive so I will just post the relevant part of it.

'Open source file


Dim fNameAndPath As Variant
fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLSX), *.XLSX", Title:="Template file To Be Open")
If fNameAndPath = False Then Exit Sub
Workbooks.Open Filename:=fNameAndPath
Dim w As Workbook
Set w = ActiveWorkbook


'Copy data from source file
' Copy index data
w.Activate

Sheets("Index").Visible = True
Sheets("Index").Select
Range("A11:H250").Select
Selection.Copy
WRK.Activate
Sheets(NewMC).Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=True, Transpose:=False
Application.CutCopyMode = False

This is the current code and as I said it works fine. The problem is, that users (and I can't stop them because they are the managers) keep adding sheets or change the name of the sheet so I can't rely on "Sheet1" or "sheet name".

My question is,
What is the code to be added that will allow the person making the importing exercise to select the tab where the information is instead of the current code that automatically selects the tab?

Thank you for looking and all the help is much appreciated.

Husoi

 

[steve the fish]

Use the sheet code name. Doesnt matter if the sheet name is changed then as it retains the code name. You can find them in the project explorer window in VBA. The sheet name is the name in brackets, the code name is the one not in brackets so for example you can say:

Set sh = Sheet1

instead of

Set sh = Sheets("Index")

 

[footoo]

Use the sheet CodeNames : https://www.ozgrid.com/VBA/excel-vba-sheet-names.htm

 

[husoi]

Thank you Steve and Footoo,

that would work if these muppets didn't add a copy of the Index and then delete the original one. This changes the SheetXX number and because they are dyslexic I have found "Index.", "Index ", "New Index" and much more.
This is why I need to create the manual selection of the tab to be selected.

But thank you for the replies

 

[steve the fish]

If i cant trust the user id be running the macros on a master workbook then issuing a copy workbook for them to destroy.

 

[husoi]

If i cant trust the user id be running the macros on a master workbook then issuing a copy workbook for them to destroy.

Have you ever heard "stupidity advantage on intelligence is that stupidity has no limits?"
Sooooo true
I spent days improving and making the master book (the one they are supposed to add their information) work properly but we are talking about users that make additions on a calculator and add the result on a cell...

 

[steve the fish]

All that said you could do something like:

Sub macro1()

Dim shName As String

shName = InputBox("Please enter your sheet name")

If SheetExists(shName) Then
    Set sh = Sheets(shName)
    sh.Select
Else
    MsgBox "Incorrect sheet name please try again"
    Exit Sub
End If

End Sub

Function SheetExists(sh As String) As Boolean

Dim sht As Worksheet

On Error Resume Next
Set sht = Sheets(sh)
On Error GoTo 0

SheetExists = Not sht Is Nothing

End Function

 

[husoi]

All that said you could do something like:

.../...

On Error Resume Next
Set sht = Sheets(sh)
On Error GoTo 0

SheetExists = Not sht Is Nothing

End Function[/CODE]

Thank you Steve,

That could work but the admin person would need an eagle eye and a good memory to make it work
I just found this oldie post from mikerickson that does the trick:

Dim uiValue as String

Rem some code

Application.Goto ThisWorkbook.Sheets("Sheet1").Range("C10")

uiValue = Application.InputBox("Enter Something", type:=2)

If uiValue = "False" Then Exit Sub: Rem cancel pressed

ThisWorkbook.Sheets("Sheet1").Range("C10").Value = uiValue

Rem continue on​

Just have to adjust to suit.

Thank you for the help.
That's why this forum is the best

 

[MARK858]

Thank you Steve and Footoo,

that would work if these muppets didn't add a copy of the Index and then delete the original one. This changes the SheetXX number and because they are dyslexic I have found "Index.", "Index ", "New Index" and much more.
This is why I need to create the manual selection of the tab to be selected.

But thank you for the replies

Sorry but am I misreading this? The other posters are talking about the Codename not the Index. It doesn't matter what sheet they delete as long as it isn't the actual sheet, it's Codename isn't affected by another sheet being deleted.

 

[husoi]

Hi Mark,

the problem I have is a bit more complicated than that.
The process is:
the first guy fills a complicated spreadsheet (call it A) with the relevant information (and most of the time starts on the wrong foot) then,
another guy (usually the first guy's manager whom isn't better tan the first one ) gets some info from A and adds it to my source workbook (B), the way they usually do it is by copy the sheet in A to B and delete the identical sheet in B as they would be duplicated.
this results in the sheetX original being replaced with sheetY hence I can't use the sheetX reference. because they have no idea what they are doing the sheet name will change depending on their mood. so I can't use the normal VBA code calling the original sheet name.

Anyhoo,

think I found a way around it with a code that looks for the 2 words I know will always be there adding wildcard *
Just in case anyone needs something like this, here is the code:

Sub FindWS()
Dim shwt As Worksheet
Dim indx As String

For Each shwt In ActiveWorkbook.Worksheets
If shwt.Name Like "*Mec*Index*" Then
indx = shwt.Name
Else
End If
Next
msgbox indx


End Sub

Note, the msgbox is just to make sure it works and will be replaced with the rest of the code (this is still work in progress).

Thank you all for all the assistance and comments.
Hu