Macro problem after copying an Excel File
in [General]
|
Prev: Excel properties that update when Excel is accessed without saving
Next: can I use conditional formating with result be hiding a row?
From: Peter on 6 Mar 2010 10:10 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
From: Gary Keramidas on 6 Mar 2010 10:30 post the code that causes the debug? do you explicitly use the filename in any of the macros? -- Gary Keramidas Excel 2003 "Peter" <pjr(a)talktalk.net> wrote in message news:%237EDy8TvKHA.1964(a)TK2MSFTNGP04.phx.gbl... > 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 >
From: Peter on 6 Mar 2010 11:59 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
From: Gord Dibben on 6 Mar 2010 15:56 Application.Run ("Accounts 2010-11.xls!Vouchers") Gord Dibben MS Excel MVP On Sat, 06 Mar 2010 16:59:39 +0000, Peter <pjr(a)talktalk.net> 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
From: Dave Peterson on 6 Mar 2010 18:00
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 |