Update sheet name in formulas with VBA

deutz

New member
Joined
May 28, 2012
Messages
27
Reaction score
0
Points
0
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?
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
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.
 

deutz

New member
Joined
May 28, 2012
Messages
27
Reaction score
0
Points
0
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
 
Top