Macro Question

  • Thread starter Thread starter JimS
  • Start date Start date
J

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?
 
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.
 
Make sure your macro is in a standard module, not the worksheet code area.
Remove sheet-specific references (sheetnames) in the macro.
 
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
 
You may not need to re-record, perhaps just edit it. Just post you macro and
we will take a look at it.
 
Instead of Sheets("Sheet1").Range......... try

ActiveSheet.Range........


Gord Dibben MS Excel MVP
 
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
 
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?
 
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
 
Recording a macro is just that. It records what you did. You must massage
it. See my post
 
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
 
Back
Top