Hi All,
I need to speed the below code up any ideas?
Dim myWorksheet As Worksheet
Dim myWorksheetName As String
Dim count As Long
Dim temp As String
Dim count2 As Long
If ComboBox1.value = "" Or ComboBox2.value = "" Then
MsgBox ("Please select Month or Branch")
Exit Sub
End If
temp = ComboBox2.value
If temp = "All" Then
temp = "*"
End If
myWorksheetName = ComboBox1.value
'check if exsists
'call SheetExists
If SheetExists(ComboBox1.value) = True Then
count = Sheets(myWorksheetName).Range("A1").CurrentRegion.Rows.count
Worksheets(myWorksheetName).Range("A1:P" & count).AutoFilter _
field:=4, Criteria1:=temp
Worksheets("Sheet1").Range("A1:P2").Copy _
Destination:=Worksheets("Sheet3").Range("A1")
Worksheets(myWorksheetName).Range("A3:M" & count).Copy _
Destination:=Worksheets("Sheet3").Range("A3")
count2 = Sheets("Sheet3").Range("A1").CurrentRegion.Rows.count
count23 = count2 + 1
Sheets("Sheet3").Range("K" & count23).value = "Total"
Sheets("Sheet3").Range("L" & count23).value = "=SUM(L3:L" & count2 & ")"
Sheets("Sheet3").Range("M" & count23).value = "=SUM(M3:M" & count2 & ")"
With Sheets("Sheet3").PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
.PrintArea = ""
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0.92)
.BottomMargin = Application.InchesToPoints(0)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = True
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Me.Hide
Application.WindowState = xlMaximized
Sheets("Sheet3").Range("A1:M" & count23).PrintPreview
Application.WindowState = xlMinimized
Me.Show
Worksheets(myWorksheetName).AutoFilterMode = False
Sheets("Sheet3").Range("A1:P" & count23).value = ""
Sheets("Sheet3").Range("A1:P2").UnMerge
ThisWorkbook.Save
Else
MsgBox ("No Data For That Month Please Change")
Exit Sub
End If
Bookmarks