error trying to sort data

  • Thread starter Thread starter Matthew Dyer
  • Start date Start date
M

Matthew Dyer

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").Range("$a:$a").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("a")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$ay:$ay").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("b")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$c:$c").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("c")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$G:$G").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("d")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$I:$I").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("e")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$bG:$bG").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("f")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$k:$k").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("g")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$v:$v").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("h")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$o:$o").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("i")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$t:$t").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("j")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$m:$m").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("k")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$ae:$ae").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("l")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$bb:$bb").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("m")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$u:$u").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("n")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$n:$n").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("o")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$s:$s").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("p")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$as:$as").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("q")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$aw:$aw").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("r")


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

Workbooks(strBookName).Worksheets(strSheetName).Range("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
 
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").Range("$a:$a").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("a")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$ay:$ay").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("b")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$c:$c").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("c")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$G:$G").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("d")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$I:$I").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("e")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$bG:$bG").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("f")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$k:$k").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("g")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$v:$v").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("h")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$o:$o").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("i")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$t:$t").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("j")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$m:$m").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("k")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$ae:$ae").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("l")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$bb:$bb").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("m")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$u:$u").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("n")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$n:$n").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("o")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$s:$s").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("p")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$as:$as").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("q")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("$aw:$aw").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("r")

Workbooks("Analytics.xlsm").Worksheets("MTD").Range("bj1:bj105").Copy
_
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Range("s1:s105­")

Workbooks(strBookName).Worksheets(strSheetName).Range("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
Click to expand...

nm. it's working now.
 
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
 
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
Click to expand...
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
Click to expand...

Oops.., I forgot to turn off ScreenUpdating. Above revised to do
that...
 
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
 
Back
Top