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

 

[footoo]

Perhaps you should be looking at code to prevent sheets being added/copied/deleted.

 

[footoo]

Perhaps you should be looking at code to prevent sheets being added/copied/deleted.

Or protect the workbook structure.

 

[My Aswer Is This]

I got my first job in the US AirFoce working with spreadsheets. Cleaning up user messes.
The users were constantly overwritting formulas.
The user then not knowing how to rewrite the formula would do the calculations on a hand held calculator and then enter the results into the cell.

After several times of this I learned to always have backups. And keep those backups some place where users cannot find them. But hey it gave me a job I liked. This was back when we were using Lotus 123.
Mid 80's

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...