error trying to sort data

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
 
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
Click to expand...

nm. it's working now.
 
G

GS

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
 
G

GS

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...
 
G

GS

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

type mismatch error 7
Another 'Copy To The Next Available Row' Question 2
Sort and align 2 ranges 1
Copy data from one sheet to another 0
Compare and Copy/Paste b/w Two Workbooks 17
To copy data from different workbooks 8
Copy data from specific sheets from one workbook to another 0
Subscript out of range? 1

Top