Macro problem after copying an Excel File
 

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

Macro problem after copying an Excel File
 

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

--


Gary Keramidas
Excel 2003


"Peter" wrote in message
...
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

Macro problem after copying an Excel File
 

On 06/03/2010 15:30, Gary Keramidas wrote:
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

Macro problem after copying an Excel File
 

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


Gord Dibben MS Excel MVP

On Sat, 06 Mar 2010 16:59:39 +0000, Peter wrote:

On 06/03/2010 15:30, Gary Keramidas wrote:
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

Macro problem after copying an Excel File
 

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 wrote:

On 06/03/2010 15:30, Gary Keramidas wrote:
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

--

Dave Peterson

Macro problem after copying an Excel File
 

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