VBA Performance

Question

I am writing some code for splitting up an excel sheet by a specific column into separate workbooks. My code works but is really slow (It should create 28 separate files and takes around 10 min per file). What can I do to make it perform better? Is there a way to save some calculation time?

Sub Split()

Dim wswb As String
Dim wssh As String
Dim path As String

Worksheets("Sheet1").Activate

wswb = ActiveWorkbook.Name
wssh = ActiveSheet.Name

path = Worksheets("Start").Range("H6").Value

Columns("H").Copy
Worksheets("Settings").Activate

Range("A1").PasteSpecial
Columns("A").RemoveDuplicates Columns:=1, Header:=xlYes

vCounter = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To vCounter

    vFilter = Sheets("Settings").Cells(i, 1)
    Sheets(wssh).Activate
    ActiveWorkbook.Worksheets("Sheet1").AutoFilterMode = False
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=8, Criteria1:=vFilter
    Cells.Copy
    Workbooks.Add
    Range("A1").PasteSpecial
    Worksheets("Sheet1").Name = "OTD"
    Sheets.Add After:=ActiveSheet
    ActiveCell.FormulaR1C1 = ""
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "PPM"
    Sheets("OTD").Select
    If vFilter <> "" Then
        ActiveWorkbook.SaveAs path & "OTD_PPM_Report_" & Format(DateSerial(Year(Date), month(Date) - 1, 1), "mmm_yyyy") & "_" & Range("I2").Value & ".xlsx"
    End If
    ActiveWorkbook.Close
    Workbooks(wswb).Activate
Next i

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=8

End Sub

Your code may be optimized to save a little time, but your longest time spent in your application will always be the file I/O, especially if you're saving to a network folder. The time is spent for Excel to create a brand-new, empty workbook and then to save it to the (remote and/or network) disk/folder. — PeterT, Oct 07 '19 at 19:58
10min per file is *crazy* slow - have you checked which line(s) are taking all of that time? Is it the SaveAs, or something else? — Tim Williams, Oct 07 '19 at 21:05
You remove filters and then reinstate them *for every single value* of `vCounter`, for one. Involving the clipboard to (presumably) just copy a data point (/cell value?) to one specific cell is another slow thing. How many rows are we talking about? `Cells.Copy` is copying *the entire bajillion cells on the sheet* when very likely only a small fraction of these cells are actually needed, and so on. Then yeah, I/O is slow, by definition. — Mathieu Guindon, Oct 07 '19 at 21:15

score 0 · Answer 1 · answered Oct 07 '19 at 20:00

0

In general I prefer to use:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

to increase performance every time dealing with excel files. I also use:

Application.Visible = False

which I also believe increase speed.

These are just general remarks.

Ps. Also try to avoid Activate / Select (this may help How to avoid using Select in Excel VBA)

answered Oct 07 '19 at 20:00

Teamothy

2,000
3
16
26

4

Turning off calculation and screen updating will have little to no impact whatsoever if your code is efficient in the first place. *Systematically* turning them off amounts to shoving all code inefficiencies under the proverbial carpet. Don't do this. Write efficient code instead - avoiding `Select` and `Activate` being the FIRST thing to do. IOW the footnote should be the answer, and the answer should be the footnote. – Mathieu Guindon Oct 07 '19 at 20:05

VBA Performance

1 Answers1