Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Changing Worksheetnames in VBA code

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is there a way to make sure the Worksheetnames are changed in the VBA code as well?
    ANy good programming tip for future and how to correct existing VBA codes?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ActiveWorkbook.SaveAs Filename:="C:My DocumentsJackintheUK 1.xls", FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False

    Does that help?

    Also tips.. stick on this site, i spend millions of hours reading and saving playing and learning, the richest source in the work is right here, if your stuck post a question to getthe answer.

    wanna really learn answer hundreds of questons will do just that.


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have got some legacy VBA code that has sheet names hardwired in the code. So, whenever any of the Worksheets name in the workbook is changed, I need to change that in the VBA code.
    Is there a way to change the VBA code so that whenever user changes teh name of the worksheet, it is automatically changed in the code!
    Thanks,
    K


  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-13 15:27, karraj wrote:
    I have got some legacy VBA code that has sheet names hardwired in the code. So, whenever any of the Worksheets name in the workbook is changed, I need to change that in the VBA code.
    Is there a way to change the VBA code so that whenever user changes teh name of the worksheet, it is automatically changed in the code!
    Thanks,
    K

    Not really....BUT what you should do is make
    a note of the worksheets CODE name and then
    do a Replace........

    What I'm trying to explain is that the Tab
    name / Sheet name has a Code name...if you
    are in the VBA project explorer you will
    see these names to the left of the Bracketed
    sheet/tab name. eg.

    Sheet1(Mysheet1 name on the tab)
    Sheet2(Mysheet2 name on the tab)

    etc. The code name remains the same even if
    you change the sheet/Tab name.

    So you should replace ALL references to a sheet
    name to the code name eg using the above as
    an example your current code may read......

    Sheets("mysheet1 name on the tab").Select
    ActiveCell.FormulaR1C1 = "23"

    Sheets("mysheet2 name on the tab").Select
    ActiveCell.FormulaR1C1 = "23"

    Sheets("mysheet1 name on the tab").Range("B14").FormulaR1C1 = "66"

    changes to;

    Sheet1.Select
    ActiveCell.FormulaR1C1 = "23"

    Sheet2.Select
    ActiveCell.FormulaR1C1 = "23"

    Sheet1.Range("B14").FormulaR1C1 = "66"


    HTH


    Ivan



  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ivan,
    That was a nice suggestion indeed. This replacement will solve my problem, but on the flip side it makes the code less readable - I mean Sheet1 makes less sense than the name of the sheet1
    Is there a way to change that to something more userfriendly.
    Thanks,
    K

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •