Error when recording macro with excel 2007

Topic

New Reply

Hi there,

I am trying to record simple macro in excel 2007 as below

Sub Macro1()
‘
‘ Macro1 Macro
‘

‘
Range(“A1”).Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFilter
ActiveSheet.Range(“$A$1:$AG$967”).AutoFilter Field:=4, Criteria1:= _
“511000-Advance-”
Selection.Copy
Sheets.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Worksheets(“Sheet3”).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(“Sheet3”).Sort.SortFields.Add Key:=Range(“C2:C3”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(“Sheet3”).Sort
.SetRange Range(“A1:AG3”)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.Style = “Comma”
Selection.NumberFormat = “_(* #,##0.0_);_(* (#,##0.0);_(* “”-“”??_);_(@_)”
Selection.NumberFormat = “_(* #,##0_);_(* (#,##0);_(* “”-“”??_);_(@_)”
Columns(“A:A”).Select
Selection.NumberFormat = “[$-409]d/mmm/yy;@”
Range(“H2”).Select
ActiveWindow.FreezePanes = True
Columns(“M:AH”).Select
Selection.Delete Shift:=xlToLeft
Sheets(“Sheet3”).Select
Sheets(“Sheet3”).Name = “Advance Register”
Range(“D21”).Select
End Sub

but always appears errors (debug message)
I don’t know what was wrong, which did not happen using 2003

thanks in advance

Indra

Reply | Quote

Replies

We may need to see a copy of your workbook to fully understand this, but if you can provide the following information then someone may have an idea for you.

[*]Exactly what error message do you get?

[*]If you click the “debug” button on the error message, which line of code is highlighted?

Reply | Quote

We may need to see a copy of your workbook to fully understand this, but if you can provide the following information then someone may have an idea for you.

[*]Exactly what error message do you get?

[*]If you click the “debug” button on the error message, which line of code is highlighted?

Please find as attached for the file,

(just realize, that allowed file uploaded has been increased..)

regards

Indra

Reply | Quote

Your macro fails at the line

Code:

ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear

because there isn’t a worksheet called “Sheet3”, the only worksheets at that time are “Sheet2”, “Sheet1”, “01-General Ledger” and “04-Chart of Account”.

This is nothing to do with Excel 2007, it fails on Excel 2003 for me. Please check what changes you have made to your workbook since it used to work.

Reply | Quote

Ya, and perhaps my question should be how to modify this macro (I knew only how to record,) so every time user create new sheet, range of data has been selected will be copied to the new sheet

regards

Indra

Reply | Quote

Hi Stuart,

I think I can modify some of the macro steps, simply copy to certain name of new worksheet. will get back to this forum, if can not

Thanks for giving hint

Indra

: )

Reply | Quote

You could create a variable of type Worksheet, set that variable to point to your newly created sheet, and then use it when you want to make changes.

For example, put

Code:

Dim sht As Worksheet

once at the beginning of your code.

Replace code like…

Code:

Sheets.Add

with…

Code:

Set sht = Sheets.Add

and replace code like

Code:

ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear

with

Code:

sht.Sort.SortFields.Clear

Finally add

Code:

Set sht = Nothing

just before End Sub, to release the memory that this variable was using.

Reply | Quote

Thanks for advise Stuart,

regards,

Indra

Reply | Quote