ExcelBanter - error trying to sort data

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - error trying to sort data (https://www.excelbanter.com/excel-programming/444453-error-trying-sort-data.html)

error trying to sort data

I have no idea why this code isn't working. I've used it in other
macros but it isnt working here. Don't know why. Frustraited beyond
belief.

Sub Print_MTD2()
Application.ScreenUpdating = False
Dim WBNew As Workbook
Dim WSNew As Worksheet
Dim strBookName As String
Dim strSheetName As String

'build new Workbook/worksheet to copy data into
Set WSNew = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
Set WBNew = ActiveWorkbook

strBookName = ActiveWorkbook.Name
strSheetName = ActiveSheet.Name

'copy columns from MTD to new sheet
Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$a:$a").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("a")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$ay:$ay").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("b")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$c:$c").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("c")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$G:$G").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("d")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$I:$I").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("e")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$bG:$bG").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("f")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$k:$k").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("g")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$v:$v").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("h")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$o:$o").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("i")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$t:$t").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("j")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$m:$m").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("k")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$ae:$ae").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("l")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$bb:$bb").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("m")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$u:$u").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("n")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$n:$n").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("o")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$s:$s").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("p")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$as:$as").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("q")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$aw:$aw").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("r")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("bj1:bj105").Copy
_
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Range("s1:s105")

Workbooks(strBookName).Worksheets(strSheetName).Ra nge("s1:s105").Formula
= "=""Last Updated - ""& Text(Max(A:A), ""mm/dd/yy"")"

Cells.EntireColumn.AutoFit

'clear filters/groups and show group column level 1
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1

'unmerge all cells, clear filters and short by column e (agent name)
ascending
'Range("a1").Select
'Range(Selection, Selection.SpecialCells(xlLastCell)).Select
'Selection.UnMerge

'**ERROR OCCURS HERE**
ActiveWorkbook.Worksheets(strSheetName).AutoFilter .Sort.SortFields.Add
Key:=Range( _
"e2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets(strSheetName).AutoFilter .Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

end sub

error trying to sort data

On Apr 15, 8:28*am, Matthew Dyer wrote:
I have no idea why this code isn't working. I've used it in other
macros but it isnt working here. Don't know why. Frustraited beyond
belief.

Sub Print_MTD2()
Application.ScreenUpdating = False
Dim WBNew As Workbook
Dim WSNew As Worksheet
Dim strBookName As String
Dim strSheetName As String

'build new Workbook/worksheet to copy data into
Set WSNew = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
Set WBNew = ActiveWorkbook

strBookName = ActiveWorkbook.Name
strSheetName = ActiveSheet.Name

'copy columns from MTD to new sheet
Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$a:$a").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("a")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$ay:$ay").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("b")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$c:$c").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("c")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$G:$G").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("d")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$I:$I").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("e")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$bG:$bG").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("f")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$k:$k").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("g")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$v:$v").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("h")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$o:$o").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("i")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$t:$t").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("j")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$m:$m").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("k")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$ae:$ae").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("l")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$bb:$bb").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("m")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$u:$u").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("n")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$n:$n").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("o")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$s:$s").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("p")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$as:$as").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("q")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("$aw:$aw").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("r")

Workbooks("Analytics.xlsm").Worksheets("MTD").Rang e("bj1:bj105").Copy
_
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Range("s1:s105*")

Workbooks(strBookName).Worksheets(strSheetName).Ra nge("s1:s105").Formula
= "=""Last Updated - ""& Text(Max(A:A), ""mm/dd/yy"")"

Cells.EntireColumn.AutoFit

'clear filters/groups and show group column level 1
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1

'unmerge all cells, clear filters and short by column e (agent name)
ascending
'Range("a1").Select
'Range(Selection, Selection.SpecialCells(xlLastCell)).Select
'Selection.UnMerge

'**ERROR OCCURS HERE**
ActiveWorkbook.Worksheets(strSheetName).AutoFilter .Sort.SortFields.Add
Key:=Range( _
* * "e2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
* * xlSortNormal
With ActiveWorkbook.Worksheets(strSheetName).AutoFilter .Sort
* * .Header = xlYes
* * .MatchCase = False
* * .Orientation = xlTopToBottom
* * .SortMethod = xlPinYin
* * .Apply
End With

end sub

nm. it's working now.

error trying to sort data

Thought you also might like to shorten the amount of lines of code for
the copy part something like this!

Sub Print_MTD3()
Dim sz, st
Dim wksSource As Worksheet, wksTarget As Worksheet

Const sSourceRanges As String = _
"$A:$A,$AY:$AY,$C:$C,$G:$G,$I:$I,$BG:$BG,$K:$K ,"
_
&
"$V:$V,$O:$O,$T:$T,$M:$M,$AE:$AE,$BB:$BB,$U:$U ," _
& "$N:$N,$S:$S,$AS:$AS,$AW:$AW,BJ1:BJ105"
Const sTargetRanges As String = _
"A:A,B:B,C:C,D:D,E:E,F:F,G:G,H:H,I:I,J:J,K:K," _
& "L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,S1:S105"
Const sFormula1 As String = _
"=""Last Updated - ""&TEXT(Max(A:A),""mm/dd/yy"")"

Set wksSource = Workbooks("Analytics.xlsm").Sheets("MTD")
Workbooks.Add xlWBATWorksheet
Set wksTarget = ActiveSheet

For Each sz In Split(sSourceRanges, ",")
For Each st In Split(sTargetRanges, ",")
wksSource.Range(sz).Copy wksTarget.Range(st)
Next 'st
Next 'sz
With wksTarget
.Range("S1:S105").Formula = sFormula1
If .FilterMode Then .ShowAllData
.Outline.ShowLevels ColumnLevels:=1
.Columns("A").UnMerge
.Columns("E").Sort Key1:=Range("E2"), Order1:=xlAscending
End With
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

error trying to sort data

GS submitted this idea :
Thought you also might like to shorten the amount of lines of code for the
copy part something like this!

Sub Print_MTD3()
Dim sz, st
Dim wksSource As Worksheet, wksTarget As Worksheet

Const sSourceRanges As String = _
"$A:$A,$AY:$AY,$C:$C,$G:$G,$I:$I,$BG:$BG,$K:$K ," _
& "$V:$V,$O:$O,$T:$T,$M:$M,$AE:$AE,$BB:$BB,$U:$U ," _
& "$N:$N,$S:$S,$AS:$AS,$AW:$AW,BJ1:BJ105"
Const sTargetRanges As String = _
"A:A,B:B,C:C,D:D,E:E,F:F,G:G,H:H,I:I,J:J,K:K," _
& "L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,S1:S105"
Const sFormula1 As String = _
"=""Last Updated - ""&TEXT(Max(A:A),""mm/dd/yy"")"

Set wksSource = Workbooks("Analytics.xlsm").Sheets("MTD")
Workbooks.Add xlWBATWorksheet
Set wksTarget = ActiveSheet

Application.ScreenUpdating = False
For Each sz In Split(sSourceRanges, ",")
For Each st In Split(sTargetRanges, ",")
wksSource.Range(sz).Copy wksTarget.Range(st)
Next 'st
Next 'sz
With wksTarget
.Range("S1:S105").Formula = sFormula1
If .FilterMode Then .ShowAllData
.Outline.ShowLevels ColumnLevels:=1
.Columns("A").UnMerge
.Columns("E").Sort Key1:=Range("E2"), Order1:=xlAscending
End With
End Sub

Oops.., I forgot to turn off ScreenUpdating. Above revised to do
that...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

error trying to sort data

Also, 2nd For..Next loop isn't right way to go. Here's a revised sub
that works right...

Sub Print_MTD3()
Dim sz, st, i As Integer
Dim wksSource As Worksheet, wksTarget As Worksheet

Const sSourceRanges As String = _
"$A:$A,$AY:$AY,$C:$C,$G:$G,$I:$I,$BG:$BG,$K:$K ,"
_
&
"$V:$V,$O:$O,$T:$T,$M:$M,$AE:$AE,$BB:$BB,$U:$U ," _
& "$N:$N,$S:$S,$AS:$AS,$AW:$AW,BJ1:BJ105"
Const sTargetRanges As String = _
"A:A,B:B,C:C,D:D,E:E,F:F,G:G,H:H,I:I,J:J,K:K," _
& "L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,S1:S105"
Const sFormula1 As String = _
"=""Last Updated - ""&TEXT(Max(A:A),""mm/dd/yy"")"

Set wksSource = Workbooks("Analytics.xlsm").Sheets("MTD")
Workbooks.Add xlWBATWorksheet
Set wksTarget = ActiveSheet

st = Split(sTargetRanges, ",")
For Each sz In Split(sSourceRanges, ",")
wksSource.Range(sz).Copy wksTarget.Range(st(i)): i = i + 1
Next 'sz
With wksTarget
.Range("S1:S105").Formula = sFormula1
If .FilterMode Then .ShowAllData
.Outline.ShowLevels ColumnLevels:=1
.Columns("A").UnMerge
.Columns("E").Sort Key1:=Range("E2"), Order1:=xlAscending
End With
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc