Macro problem after copying an Excel File

[Peter]

Hello,

Can anyone help please?

I have a file 2009xxx.xls with many macros. I now wish to copy and call it 2010xxx.xls
but the macro's won't run and advise that it needs debugging.
Is there any way to update them without re-recording? I've tried to edit the names but
they don't run.

Kind regards
Peter

 

[Gary Keramidas]

post the code that causes the debug? do you explicitly use the filename in
any of the macros?

 

[Peter]

post the code that causes the debug? do you explicitly use the filename
in any of the macros?

Gary,

Here is a portion of the code, the penultimate line is highlighted when the macro runs. I
copied the original file 'Accounting.xls' and renamed it 'Accounts 2010-11.xls'. Perhaps
Excel is not intelligent enough to realise! :


Sub report_VOUCHERS()
'
' report_VOUCHERS Macro
' Macro recorded 08/02/2010 by Peter Rawbone
'

'
Cells.Select
With Selection
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.ClearContents
Selection.ColumnWidth = 8.43
Selection.RowHeight = 12.75
Range("A1").Select
Application.Run "Accounts 2010-11.xls!Vouchers"
Rows("1:1").Select

Any advice greatly appreciated

Peter

 

[Gord Dibben]

Application.Run ("Accounts 2010-11.xls!Vouchers")


Gord Dibben MS Excel MVP

 

[Dave Peterson]

I'd try:
Application.Run "'Accounts 2010-11.xls'!Vouchers"

I think you'll need the extra apostrophes since the name has those spaces in it.

And you'll have to make sure that this file is open when you start the macro.

 

[Peter]

My grateful thanks to all who responded so quickly and accurately. The addition of the
extra apostrophes made all the difference. A quick VBA edit and all works as it should.

Easy when you know how!

Enjoy your day

Kind regards

Peter