Macro problem after copying an Excel File

  • Thread starter Thread starter Peter
  • Start date Start date
P

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
 
post the code that causes the debug? do you explicitly use the filename in
any of the macros?
 
post the code that causes the debug? do you explicitly use the filename
in any of the macros?
Click to expand...
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
 
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.
 
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
 
Back
Top