YasserKhalil
Well-Known Member
Hello everyone
I have a userform from which I would select some parameters so as to finally create new workbooks based on the selected items from listbox
The code works fine .. but as for the original data, it takes so much time ..
This code in the userform module (the userform name is "Settings")
Select the sheet 9100
Select the header CUSTNU
and in the listbox select some items and finally click "Create Workbooks" button
The code works fine for me ..
What I am seeking for is to make it faster ...?
Any ideas my friends
The thread is posted here too
https://www.excelforum.com/excel-pr...ooks-accelerate-the-code-with-large-data.html
I have a userform from which I would select some parameters so as to finally create new workbooks based on the selected items from listbox
The code works fine .. but as for the original data, it takes so much time ..
Code:
Private Sub cmdCreateWorkbooks_Click()
Dim x, ws As Worksheet, i As Long, lr As Long
If cbSheet.Value = "" Then MsgBox "You Have To Select Sheet", vbCritical: Exit Sub
If cbHeader.Value = "" Then MsgBox "You Have To Select Header", vbCritical: Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set ws = ThisWorkbook.Worksheets(CStr(cbSheet))
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
For i = 0 To lstParameters.ListCount - 1
If lstParameters.Selected(i) Then
ws.Copy
With ActiveWorkbook.Sheets(1)
.Name = "Sheet1"
.Rows("1:3").Delete
x = Application.Match(cbHeader.Value, .Rows(1), 0)
If IsError(x) Then GoTo Skipper
'With wsSheet
With .ListObjects(1).DataBodyRange
.AutoFilter
.AutoFilter Field:=x, Criteria1:=CStr(lstParameters.List(i, 0))
'.EntireRow.Delete
'.AutoFilter
End With
'End With
' .Range("A1").AutoFilter Field:=x, Criteria1:="<>" & CStr(lstParameters.List(i, 0))
'
' On Error Resume Next
' .Range("A2:A" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Delete
' On Error GoTo 0
'
' .AutoFilterMode = False
' If .FilterMode = True Then .ShowAllData
'Call DelFilterParam(ActiveWorkbook.Sheets(1), .ListObjects(1), x, CStr(lstParameters.List(i, 0)))
Application.DisplayAlerts = False
.Parent.SaveAs txtPath & ws.Name & "-" & lstParameters.List(i, 0) & ".xlsx"
Application.DisplayAlerts = True
.Parent.Close False
End With
End If
Skipper:
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
MsgBox "Workbooks Created Successfully At " & txtPath, 64
End Sub
This code in the userform module (the userform name is "Settings")
Select the sheet 9100
Select the header CUSTNU
and in the listbox select some items and finally click "Create Workbooks" button
The code works fine for me ..
What I am seeking for is to make it faster ...?
Any ideas my friends
The thread is posted here too
https://www.excelforum.com/excel-pr...ooks-accelerate-the-code-with-large-data.html