Code for manually select a tab

husoi

Board Regular
Joined
Sep 12, 2012
Messages
50
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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:

Code:
Set sh = Sheet1

instead of

Code:
Set sh = Sheets("Index")
 
Upvote 0
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 :)
 
Upvote 0
If i cant trust the user id be running the macros on a master workbook then issuing a copy workbook for them to destroy.
 
Upvote 0
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 :LOL:
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...
 
Upvote 0
All that said you could do something like:

Code:
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
 
Upvote 0
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 :cool:
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
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 :LOL:) 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:confused: 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
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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