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

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
 
J

Jim Cone

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
 
C

chunt

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to get a macro to run on all files in a folder and save each one? 0
How do I tell EXCEL to use macros in ThisWorkBook. 3
Help with VBA to find data delete it change font, add lines then looptill end of file 4
Modify Recorded Macro 1
Adding to exsisting MACRO 2
Condense code 2
How to rewrite this code? (first half) 1
Excel macros page breaks but not on row 1 and column value in footer 3

Top