Results 1 to 3 of 3

Thread: Update sheet name in formulas with VBA

  1. #1

    Update sheet name in formulas with VBA



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi and thanks in advance,


    I am using Excel 2003.

    Iím trying to build a stats gathering and reporting system for the testing of software releases and Iím thinking of using two workbooks (TestersWkb and ReportsWkb). TestersWkb will be used by the software testers to enter the results of their tests and ReportsWkb will summarise that data into reports for the execs.

    For example, for release 1 of the software, Sheet1 in ReportsWkb will have links to Sheet1 in TestersWkb ...so far so good.

    Now when release 2 is available to test, I want to add another sheet to both workbooks for that release and update the links and then repeat this process for each new release.

    The sheets in TestersWkb will be identical for all releases so it is no problem to copy the previous sheet to a newly named sheet but the problem is how to copy the previous sheet in ReportWkb and update the links to point to the newly added sheet in TestersWkb. The only thing that will change in the links is the sheet name. Any idea how this could be done with VBA?

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Maybe you can just do this globally using Find and Replace (i.e. Ctrl H) Just put the old workbook name in under Find, and the new one under Replace, and make sure you have the new one open when you do this.

  3. #3
    Thanks Jeffrey,

    I think that is a good suggestion but I will need to do this in VBA to automate the process so I found some code that seems to do what I want ...

    Code:
    ActiveSheet.Range("A1:M110").Cells.Replace What:="Release1", Replacement:="Release2", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, _
        SearchFormat:=False, ReplaceFormat:=False
    Where Release1 is the previous sheetname in the formulas and Release2 is the new one

Posting Permissions

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