Macro not working after I renamed spreadsheet

[Dosaparks]

Hi,

I am a beginner when it comes to macros and don't know much about the code (I just use the record function to create them).

I have a worksheet with a lot of them and they all work fine, however I need to rename the spreadsheet and when I do this I start getting a Run-time error '9'.

I realise the problem is that all the macros I have recorded (which often jump from sheet to sheet) show the specific spreadsheet name in the code and this doesn't change when I rename the spreadsheet. Therefore the code continues to look up the old spreadsheet name which doesn't exist and I get the error.

What is the easiest option to solve this problem? Is there a way of 'hard wiring' the codes to the spreadsheet itself and not just the spreadsheet name which can change?

I just signed up to MrExcel today so hopefully my debut post gets a result!

 

[meldoc]

Hello and welcome

See the link.

http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm

 

[Smitty]

Welcome to the Board!

How about posting your code?

 

[AlphaFrog]

If you are in the macro editor, you may see a tree view in the upper left with each of your sheets listed e.g.;

• Microsoft Excel Objects
  • Sheet1(Sheet1)
  • Sheet2(MySheet)
  • Sheet3(Sheet3)
  • ThisWorkbook

The Name in parenthesis e.g. (MySheet) is the name you give the sheet. The name to the Left of parenthesis e.g. Sheet2 is the name Excel gives the Sheet when the sheet is first created. That name is called the sheet code name and it will not change. You can use the code name in your macro.

If your macro has something like this
Sheets("MySheet").Select

Change it to this...
Sheet2.Select

Then if you Change the name of MySheet, the macro will still work.


You can also select a sheet by position.
Sheets(2).Select

This will select the 2nd sheet in the sheet Order. If you move the sheets around. It will still select whichever sheet is 2nd in order.

 

[Dosaparks]

Meldoc - Thanks for your quick response. The link looks very relevant and I'm just working through it now.

Smitty - I believe this is the code below (I clicked debug when the error came up to access it). The name of the spreadsheet (entire workbook) was LAFHA Calculator however I have renamed it to abc.

Alphafrog - You post looks very relevant too. I think it spells out effectively what Meldoc was referring me too so this is very useful.

I will take this away and let you know how I go.

Much obliged to you all!


'
ActiveWindow.NewWindow
Windows.Arrange ArrangeStyle:=xlVertical
Range("F24").Select
ActiveWindow.Zoom = 85
Windows("LAFHA Calculator.xlsm:1").Activate
Sheets("Calculator").Select
ActiveWindow.Zoom = 85
ActiveWindow.Zoom = 70
Windows("LAFHA Calculator.xlsm:2").Activate
With ActiveWindow
.Width = 465.75
.Height = 624.75
End With
Windows("LAFHA Calculator.xlsm:1").Activate
With ActiveWindow
.Top = 1
.Left = 466
End With
With ActiveWindow
.Width = 496.5
.Height = 624.75
End With
Range("B26").Select
Windows("LAFHA Calculator.xlsm:2").Activate
Rows("5:5").Select
ActiveWindow.FreezePanes = True
Windows("LAFHA Calculator.xlsm:1").Activate
Rows("7:7").Select
ActiveWindow.FreezePanes = True
Range("C30").Select
End Sub

 

[Dosaparks]

The information regarding locking in the sheet code will be useful to me however I don't think it solves my problem.

The sheet names actually stay the same. It is the workbook name that I am changing.

Is there a way to lock in the actual workbook name? If you refer to the code I believe it fails when it gets to Windows("LAFHA Calculator.xlsm:1").Activate as the "LAFHA Calculator" no longer exists.

If it helps - The code is a split screen. It creates a new window and then resizes the two windows so they both take up approx half the screen and can be viewed together (without going back and forth)