Macro not working between different sheets

[Quadhurst]

I wonder if someone might help me.

I have recorded a macro in Excel 2011 that works perfectly in the sheet where I recorded it. When I apply it to another sheet - with the same layout etc but different data values, it doesn't work. Why is this and what can I do to rectify it?

Please note that I know absolutely nothing about VB code language and how to edit it! I haven't a clue.

Regards.




Andrew

[martindwilson]

lets guess, nope cant see it,, post the code here , don't forget to use code tags

[Quadhurst]

Thank you for your reply. Since I know nothing about code and tags, I hope that what I've copied below is what you want. The macro was recorded in a sheet called "1945" - where it works perfectly. When I try it on sheets named "1951" and "1955" (etc), it doesn't work.

Many thanks for your swift reply.

Regards.




Andrew



Sub try27jun()
'
' try27jun Macro
'

'
Range("AE86:AG196").Select
ActiveWindow.SmallScroll ToRight:=1
ActiveWorkbook.Worksheets("1945").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("1945").Sort.SortFields.Add Key:=Range("AG86:AG196" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("1945").Sort
.SetRange Range("AE86:AG196")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("AE86:AG196").Select
ActiveWorkbook.Worksheets("1945").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("1945").Sort.SortFields.Add Key:=Range("AF86:AF196" _
), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("1945").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("1945").Sort.SortFields.Add Key:=Range("AG86:AG196" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("1945").Sort.SortFields.Add Key:=Range("AF86:AF196" _
), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("1945").Sort
.SetRange Range("AE86:AG196")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=22
Selection.AutoFilter
ActiveSheet.Range("$AE$86:$AG$196").AutoFilter Field:=3, Criteria1:="=Lab" _
, Operator:=xlOr, Criteria2:="=Lib"
ActiveSheet.Range("$AE$86:$AG$196").AutoFilter Field:=3
ActiveSheet.Range("$AE$86:$AG$196").AutoFilter Field:=3, Criteria1:="=Con" _
, Operator:=xlOr, Criteria2:="=Lab"
ActiveSheet.Range("$AE$86:$AG$196").AutoFilter Field:=3, Criteria1:="Lab"
ActiveWorkbook.Worksheets("1945").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("1945").AutoFilter.Sort.SortFields.Add Key:=Range( _
"AG86:AG196"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("1945").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("AE129:AG194").Select
ActiveWorkbook.Worksheets("1945").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("1945").Sort.SortFields.Add Key:=Range( _
"AF129:AF194"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("1945").Sort
.SetRange Range("AE129:AG194")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Range("$AE$86:$AG$196").AutoFilter Field:=3
End Sub
Sub tryout2()
'
' tryout2 Macro
'

'
Range("AE86:AG196").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("AD84").Select
Application.CutCopyMode = False
Range("AE85:AG196").Select
ActiveWorkbook.Worksheets("1945").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("1945").Sort.SortFields.Add Key:=Range("AG86:AG196" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("1945").Sort.SortFields.Add Key:=Range("AF86:AF196" _
), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("1945").Sort
.SetRange Range("AE85:AG196")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("AG85").Select
Selection.AutoFilter
ActiveSheet.Range("$AE$85:$AG$196").AutoFilter Field:=3, Criteria1:="=Con" _
, Operator:=xlOr, Criteria2:="=Lib"
ActiveSheet.Range("$AE$85:$AG$196").AutoFilter Field:=3, Criteria1:="Con"
Range("AE85:AG128").Select
ActiveWorkbook.Worksheets("1945").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("1945").Sort.SortFields.Add Key:=Range("AF86:AF128" _
), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("1945").Sort
.SetRange Range("AE85:AG128")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Range("$AE$85:$AG$196").AutoFilter Field:=3, Criteria1:="=Con" _
, Operator:=xlOr, Criteria2:="=Lab"
ActiveSheet.Range("$AE$85:$AG$196").AutoFilter Field:=3, Criteria1:="Lab"
Range("AE85:AG214").Select
ActiveWorkbook.Worksheets("1945").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("1945").Sort.SortFields.Add Key:=Range("AF86:AF214" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("1945").Sort
.SetRange Range("AE128:AG214")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Range("$AE$85:$AG$196").AutoFilter Field:=3
ActiveWindow.SmallScroll Down:=21
ActiveWindow.SmallScroll ToRight:=-17
End Sub

[vlady]

can you wrap your code with code tags..
click edit post below you post.
then
select the entire code then click the #

[Quadhurst]

I did exactly as you advised, selected the code and then clicked the # button and the entire code disappeared. Could my keyboard be different?

Regards.



Andrew

[martindwilson]

works for me,what bits if any of this code have you changed in new sheet?

Please Login or Register  to view this content.

[Quadhurst]

As far as I am aware, absolutely nothing.

Interrestingly I recorded exactly the same macro in Excel 2003 at work and it works perfectly on all sheets when I open it up back home in Excel 2011.

Thank you fro your continuing interest in helping me.



Andrwe