1. T

    Changing SheetTab name causes VBA to fail

    Hello everyone. It has been my understanding that if we referred to a sheet by its CodeName rather than its SheetTab name, then if someone renamed the sheet, it would not cause VBA to fail. With that thought in mind, I have the following code. The purpose of this macro is to open a second...
  2. J

    Referencing code name of second workbook vba

    Morning All I have two workbooks. The active workbook does some calculations/ has some user input, and then a macro is used to move certain entries to a second workbook. Both workbooks are set up using code names as somebody will inevitably change the worksheet name at some point (!) but...
  3. Jaafar Tribak

    Cool Balloon Tooltips for Worksheet Tabs

    Hi all, I have just completed this code which as the thread title says, it adds a balloon tip to the worksheets of your choice... Having a tooltip displaying info about the sheet when placing the mouse pointer over the tab can be useful and fun. The code is based on the sheet CodeName so that...
  4. T

    Refer to another workbook's worksheet codename

    Is it possible to refer to a worksheet's codename if it's in another workbook? If the workbook I am trying to open contains a worksheet called "Data" and its codename is "wksData" then this works: Workbooks.Open C:\MyLocation\MyFile.xlsm Dim wb As Workbook Set wb = Activeworkbook Dim i As...
  5. J

    Worksheet Reference using Personal.xlsb

    Hello: I am building a macro that works with multiple other files. It is contained in the personal.xlsb file. However, when I refer to the worksheets in the other files, I'm able to use the Name property -- for example "New Sheet" -- without a problem. But when I try to use the CodeName property...
  6. Dr. Demento

    Sheet codename order in VBE

    I have a large number of worksheets (>200) and because Excel places the sheets in order of codename and because the codenames are alphanumeric, I wind up with an order like Sheet1, Sheet100, Sheet101, etc. Is there a way re-order the sheets so they are in either the same order as the tabs or at...
  7. T

    Refering to codename of worksheet

    IF I know my workbook has a worksheet called Apples, I can refer to it as follows: With wb.Worksheets("Apples") ' Do something End With but I DON'T know for certain there's a worksheet called Apples because the user might change it. Instead I DO know there's a worksheet with a...
  8. S

    CodeName value not available on imported sheets

    Hi, 1: I have an Access DB that I use to copy Worksheets X, Y, Z from WorkBook 1 to WorkBook 2 (WorkBook 2 already has Sheets (A, B & C). objWBSource.Worksheets("MySheetName").Copy Before:=objWBDest.Sheets(1) 2: I save and close both WorkBooks. 3: WorkBook 2 then has sheets A, B & C and the...
  9. T

    Immutable identifier for ListObject

    Is there any way to get an immutable identifier for a ListObject? A ListObject has a Name and Index property and belongs to a parent worksheet, but suppose I have a ListObject called 'Table1' and then I delete that table and then add a new ListObject also called 'Table1' that also has Index=1...
  10. J

    How rename worksheet CodeName at runtime?

    Hi I have a form with a combobox that contains 1,2,3,4 I need to create a work worksheet at Runtime and assign it the following properties. Name = "Week" & the value of combobox CodeName = "wks"& Worksheet name I get a compile error on the CodeName ( Can't assign to read only property) Here is...
  11. Z

    Change Named Range reference using Codename of sheet

    Dear All, could anyone please help me to change my named range cell reference, by using the codename of the particular sheet? This is what works fine, but uses the name of the sheet: ActiveWorkbook.Names("myRng").RefersTo = "='User123'!$B$39:$B$" & LastRow where the "LastRow" is a variable...
  12. A

    Problems With Worksheet Renaming

    Hello all, I am new to the world of VBA and just started working on creating macros about 5 weeks ago. I have been watching YouTube videos and reading blogs to help me get going. I have the code below that stops working when new sheets are created from the template. I realized after the fact and...
  13. T

    Can you reference worksheet by codename using variable?

    Hi, I am in need of looping macro that checks value in cells and hides columns that fit the condition, which is easy. Dim i As Integer For i = 3 To 20 Select Case Cells(2, i) Case 0 Columns(i).EntireColumn.Hidden = True Case "" Columns(i).EntireColumn.Hidden = True...
  14. R

    For Each in range name sheets with codename

    Looking to use a Range from sheet "Employee List" ("A2:A33") and name only sheets with the beginning codename "FTE*". <tbody> Codename Cell Range Result FTE01 (FTE01) A2 Todd FTE01 (Todd) FTE02 (FTE02) A3 Steve FTE02 (Steve) Table (Table) Not Renamed FTE03 (FTE03) A4 Mark FTE03...
  15. H

    Excel VBA - error ref tables

    Hi, Hope someone can help I'm getting an error on the highlighted part of the code below? This occurs when I'm referencing a sheet by its code name and not its tab name. Any help or advance?? Private Sub CommandButton1_Click() Dim lngStart As Long, lngEnd As Long lngStart =...
  16. M

    Excel Use CodeName with Name Manager without VBA

    I would like to use the CodeName with the Name Manager without using VBA. I have a spreadsheet with four years of data in it and when 2016 is done I want to change the tab name to 2020 without having to change range name references. Is there a way to put the code name in when using the...
  17. T

    How do I reference a workbook using a codename?

    So here is my problem. I have 2 open workbooks. I need to copy a set of cells and paste it to a sheet in another workbook. I have to be able to reference the code-name because the file name is always changing because of users needing to update it. Also in the best case I would like to be able...
  18. V

    Array of Worksheet CodeNames

    All, I've created an Array of worksheet 'Names', to loop through them. Instead I'd like to use the 'CodeName' so if the user changes the name on the tab, the code does not break.. Any suggestions on how to do this? Dim vWSArray as Variant, i as long Dim ws as Worksheet '- Create...
  19. T

    What would cause the codename to change?

    Hi guys, My spreadsheet codenames get changed sometimes and this caused my code to run incorrectly... I don't know what caused this error??? Can you please help me??? For example I have a worksheet codename is APPLE, and I have some private sub code in that spreadsheet APPLE.. sometimes...
  20. R

    Activate sheets by codename from the Personal.xlsb

    How do I activate/access sheets by codename from the Personal.xlsb I want it to work for 2 scenarios: 1) Recently downloaded Excel report in the temporary folder, with one sheet, Worksheet name and codename are both sheet1. 2) Workbook has been saved and the worksheet name has been changed...

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