Originally, I had code that used the data filter across the header, and cycled through each criteria in a specific row, copying+pasting all the visible data on that sheet to various corresponding sheets. I felt that this was too rudimentary, and with some help on SO, wrote new code as seen below. For a reason I am not sure of, my macro now hangs for 5-10 minutes to process the data. Compared to the data filter method, which took about 10-15 sec. Typically my worksheet is less than 1000 rows. But let's just say, absolute worst case, it's no more than 2000 rows.
Each row contains about 50 consecutive cells of text some of which have their interior filled by a color and about 10 of the 50 have EXACT or simple SUM formulas.
If anyone has any pointers as what I should change that could speed it up, that would be great! Or if you think the data filter method is best.
Const TERR As String = "NA,AU,BR,CAen,CAfr,DE,ES,FR,IT,MX,USA,UK"
Sub CATsplit(wb2)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim wbMacro As ThisWorkbook
Dim dict As New Scripting.Dictionary
Dim t As Variant
Dim newSheet As Worksheet
Dim LC As Long
Set NewWB = Application.Workbooks.Add(1)
LC = Sheet(1).Cells(1, Columns.Count).End(xlToLeft).Column
wb2.Sheets(1).Copy after:=NewWB.Sheets(NewWB.Sheets.Count)
Sheets("Sheet1").Delete
For Each t In Split(TERR, ",")
' Create each sheet
Set newSheet = Sheets.Add(after:=ActiveSheet)
newSheet.Name = t
dict.Add t, .Cells(.Rows.Count, 2).End(xlUp).Row
Next
Sheets("NA").Name = "No Result"
Sheet(1).Activate
For r = 2 To LR Step 1
If Application.WorksheetFunction.IsNA(Sheet(1).Range("K" & r)) Then
Range(Cells(r, 1), Cells(r, LC)).Copy Destination:=Sheets("No Result").Cells(dict("NA") + 1, 1)
dict("NA") = Sheets("NA").Cells(Rows.Count, "B").End(xlUp).Row
GoTo Nxt
End If
If Sheet(1).Range(Cells(r, 14)).Value = "Australia" Then
Range(Cells(r, 1), Cells(r, LC)).Copy Destination:=Sheets("AU").Cells(dict("AU") + 1, 1)
dict("AU") = Sheets("AU").Cells(Rows.Count, "B").End(xlUp).Row
GoTo Nxt
End If
If Sheet(1).Range(Cells(r, 14)).Value = "Brazil" Then
Range(Cells(r, 1), Cells(r, LC)).Copy Destination:=Sheets("BR").Cells(dict("BR") + 1, 1)
dict("BR") = Sheets("BR").Cells(Rows.Count, "B").End(xlUp).Row
GoTo Nxt
End If
'''' 9 other IF statements structured the same way
Nxt:
Next r
Aucun commentaire:
Enregistrer un commentaire