Macro Question

[JimS]

Macro question

I have a workbook where I made a macro. I copy an existing sheet when
I need to enter new data.

The problem is the macro won't work on the new sheet unless I give it
the exact same name as the original sheet where I created the macro.

Is there a way around this?

 

[Wigi]

Hello

You can use the CodeName of that sheet.

Or perhaps you can work with indexes of sheets.

Or you can set the name of the sheet using 1 line of code.

 

[Gary''s Student]

Make sure your macro is in a standard module, not the worksheet code area.
Remove sheet-specific references (sheetnames) in the macro.

 

[JimS]

New to macros, don't know much about code. I can delete this macro
and re-record a new one. How would I go about doing it so it works
the way I want it to?

Using Excel 2007

Thanks

 

[Gary''s Student]

You may not need to re-record, perhaps just edit it. Just post you macro and
we will take a look at it.

 

[Gord Dibben]

Instead of Sheets("Sheet1").Range......... try

ActiveSheet.Range........


Gord Dibben MS Excel MVP

 

[JimS]

I assume you mean edit the code. I'll paste it here. The sheet name
I always have to use is "template." I see that in the code. Could I
have just removed every instance of the word "template?"

Thanks again.


Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+J
'
ActiveWindow.SmallScroll Down:=11
Range("A22:R30").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=6
Range("A32").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=6
Range("A42").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=12
Range("A52").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=12
Range("A62").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=12
Range("A72").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-42
Range("A32:W40").Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("template").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("template").Sort.SortFields.Add
Key:=Range( _
"J33:J40"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("template").Sort
.SetRange Range("A32:W40")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=12
Range("A42:W50").Select
ActiveWorkbook.Worksheets("template").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("template").Sort.SortFields.Add
Key:=Range( _
"K43:K50"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("template").Sort
.SetRange Range("A42:W50")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=12
Range("A52:W60").Select
ActiveWorkbook.Worksheets("template").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("template").Sort.SortFields.Add
Key:=Range( _
"L53:L60"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("template").Sort
.SetRange Range("A52:W60")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=6
Range("A62:W70").Select
ActiveWorkbook.Worksheets("template").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("template").Sort.SortFields.Add
Key:=Range( _
"M63:M70"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("template").Sort
.SetRange Range("A62:W70")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=12
Range("A72:S80").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=12
Range("A82").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("template").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("template").Sort.SortFields.Add
Key:=Range( _
"S83:S90"), SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("template").Sort
.SetRange Range("A82:S90")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End SubOn Sun, 10 Aug 2008 06:20:01 -0700, Gary''s Student

 

[JimS]

One other thing: in the future when recording my macro for the first
time, how can I avoid the same problem for my next macro?

 

[Don Guillett]

try this. It probably can be further refined but I'm still not too familiar
with 2007 sorting

Sub Macro1()
Keyboard Shortcut: Ctrl+Shift+J

range("A22:R30").Copy
Range("A32,a42,a52,a62,a72").PasteSpecial Paste:=xlPasteValues

activesheeet.Sort.SortFields.Clear
activesheet.Sort.SortFields.Add' Key should probably be ONE cell such as
J33??
Key:=Range( _
"J33:J40"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
With activesheet.Sort
.SetRange Range("A32:W40")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

activesheeet.Sort.SortFields.Clear
activesheet.Sort.SortFields.Add
Key:=Range( _
"K43:K50"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
With activesheet.Sort
.SetRange Range("A42:W50")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

activesheet.Sort.SortFields.Clear
activesheet.Sort.SortFields.Add
Key:=Range( _
"L53:L60"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
With activesheet.Sort
.SetRange Range("A52:W60")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

activesheet.Sort.SortFields.Clear
activesheet.Sort.SortFields.Add
Key:=Range( _
"M63:M70"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
With activesheet.Sort
.SetRange Range("A62:W70")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Range("A72:S80").Copy
Range("A82").PasteSpecial Paste:=xlPasteValues

activesheet.Sort.SortFields.Clear
activesheet.Sort.SortFields.Add
Key:=Range( _
"S83:S90"), SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:= _
xlSortNormal
With activesheet.Sort
.SetRange Range("A82:S90")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
end with
end sub

 

[Don Guillett]

Recording a macro is just that. It records what you did. You must massage
it. See my post

 

[JimS]

thank you for doing all this work, but when I ran the macro I got an
error saying "sub or function not defined."

The word keyboard was highlighted in the macro. Sub Macro was in
yellow.

Sub Macro1()
Keyboard Shortcut: Ctrl Shift + J

 

[Gord Dibben]

The Keyboard line is a comment.

Add an apostrophe before the K


Gord Dibben MS Excel MVP