difference in macro recording from 2003 to 2007 excel killing me.

  • Thread starter Thread starter chunt
  • Start date Start date
C

chunt

Hello,

I'm trying to figure out why there is a difference like this between
excel 2003 and 2007. Perhaps there's a setting somewhere that I need
to change.

When I record a macro in 2003 to the personal workbook, that macro
then works for me in every document that I open thereafter and try to
run it in. In 2007 it seems to associate my selecting with the
particular file that I record the macro in, so that when I go to run
it later on a different report, but I want to do the same changes, it
comes up with an error because it is searching for the selection in
the other document name. Hopefully I've explained it alright.

I'm posting bellow first the 2003 macro that works in all subsequent
documents first and then a similar macro from 2007. If someone could
please help me I'd be very grateful.

Thanks,


2003:

Sub reports()
'
' reports Macro
' Macro recorded 1/3/2008 by xxxx
'
' Keyboard Shortcut: Ctrl+z
'
Cells.Select
Cells.EntireColumn.AutoFit
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
Columns("E:L").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.ColumnWidth = 17.29
Columns("B:B").ColumnWidth = 6.43
Columns("A:A").ColumnWidth = 11.43
Columns("A:B").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1").Select
End Sub


2007

Sub test()
'
' test Macro
' test
'
' Keyboard Shortcut: Ctrl+t
'
Cells.Select
Cells.EntireColumn.AutoFit
Range("D1").Select
ActiveWorkbook.Worksheets("*****file name or tab name not sure
which is is*****").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("*****file name or tab name not sure
which is is*****").Sort.SortFields. _
Add Key:=Range("D1:D372"), SortOn:=xlSortOnValues,
Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("*****file name or tab name not
sure which is is*****").Sort
.SetRange Range("A1:E372")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("E:L").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.ColumnWidth = 17.29
Columns("B:B").Select
Selection.ColumnWidth = 15.14
Columns("A:A").Select
Selection.ColumnWidth = 16
Columns("A:B").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1").Select
End Sub
 
I don't have xl2007 but the change shown below should allow you to sort on any sheet in xl2007.
The change will not allow you to sort in prior versions of Excel as
the recorder is using terms that are not recognized by earlier versions.
My recommendation is to just replace the xl2007 recorded code with the XL2003 version.
'--
Replace (all instances of):
ActiveWorkbook.Worksheets("tab name")

With"
ActiveSheet
'--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



<[email protected]>
wrote in message
Hello,

I'm trying to figure out why there is a difference like this between
excel 2003 and 2007. Perhaps there's a setting somewhere that I need
to change.

When I record a macro in 2003 to the personal workbook, that macro
then works for me in every document that I open thereafter and try to
run it in. In 2007 it seems to associate my selecting with the
particular file that I record the macro in, so that when I go to run
it later on a different report, but I want to do the same changes, it
comes up with an error because it is searching for the selection in
the other document name. Hopefully I've explained it alright.

I'm posting bellow first the 2003 macro that works in all subsequent
documents first and then a similar macro from 2007. If someone could
please help me I'd be very grateful.

Thanks,


2003:

Sub reports()
'
' reports Macro
' Macro recorded 1/3/2008 by xxxx
'
' Keyboard Shortcut: Ctrl+z
'
Cells.Select
Cells.EntireColumn.AutoFit
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
Columns("E:L").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.ColumnWidth = 17.29
Columns("B:B").ColumnWidth = 6.43
Columns("A:A").ColumnWidth = 11.43
Columns("A:B").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1").Select
End Sub


2007

Sub test()
'
' test Macro
' test
'
' Keyboard Shortcut: Ctrl+t
'
Cells.Select
Cells.EntireColumn.AutoFit
Range("D1").Select
ActiveWorkbook.Worksheets("*****file name or tab name not sure
which is is*****").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("*****file name or tab name not sure
which is is*****").Sort.SortFields. _
Add Key:=Range("D1:D372"), SortOn:=xlSortOnValues,
Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("*****file name or tab name not
sure which is is*****").Sort
.SetRange Range("A1:E372")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("E:L").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.ColumnWidth = 17.29
Columns("B:B").Select
Selection.ColumnWidth = 15.14
Columns("A:A").Select
Selection.ColumnWidth = 16
Columns("A:B").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1").Select
End Sub
 
Hi Jim,

Thanks for the answer posted. However, I'm trying to get this so that
my staff can record the macros themselves without having to edit them
in the macro editor. They are not familiar with that part of excel.
I'm trying to avoid having to go to all of their computers to make
changes. I was hoping more for some setting change or something like
that that would help with this.

Thanks,
Chris.
 
Back
Top