+ Reply to Thread
Results 1 to 2 of 2

Pages Generated by macro are slow

Hybrid View

  1. #1
    Registered User
    Join Date
    11-14-2011
    Location
    fairfax, virginia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Pages Generated by macro are slow

    I work at a landscape architecture firm, and was tasked with updating our office master plant list. I used the following code to generate and save individual plantlists, and it works! but it's incredibly slow. the files that is generates (which have no code on them) are extremely slow and i don't have the slightest clue why. it takes nearly 10 seconds to update any of the fields in the generated workbook. does anyone have an idea as to why this happens and how it can be resolved?

    Sub generatePL()
    Application.ScreenUpdating = False
        Call Workbook
        Call SortCopyPaste
    Application.ScreenUpdating = True
    End Sub
    Sub Workbook()
        Dim strName As String
        Dim LValue As String
        Dim Fname As String
        Dim DTAddress As String
        Dim wkbkname As String
        strName = Application.InputBox("Enter client Name")
        DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
        LValue = format(Date, "yyyy.mm.dd")
        Fname = DTAddress & strName & "." & LValue & "." & "plantlist" & ".xls"
        wkbkname = strName & "." & LValue & "." & "plantlist" & ".xls"
        Set NewBook = Workbooks.Add
            With NewBook
                .Title = Fname
                .Subject = "Plantlist"
                .SaveAs Filename:=Fname
                .Activate
                ActiveSheet.name = strName & " " & "Plantlist"
            End With
    
        Call Formatting
        Call DelSht
    
    End Sub
    Function DelSht()
        Application.DisplayAlerts = False
        Sheets("Sheet2").Delete
        Sheets("Sheet3").Delete
        Application.DisplayAlerts = True
    End Function
    Function Formatting()
        ScreenUpdating = False
        
        'Set Layout and page view
        ActiveWindow.View = xlPageLayoutView
        If Val(Application.Version) & gt = 14 Then Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .LeftMargin = Application.InchesToPoints(0.25)
            .RightMargin = Application.InchesToPoints(0.25)
            .TopMargin = Application.InchesToPoints(0.75)
            .BottomMargin = Application.InchesToPoints(0.75)
            .HeaderMargin = Application.InchesToPoints(0.3)
            .FooterMargin = Application.InchesToPoints(0.3)
            .PrintHeadings = False
            .PrintGridlines = False
            .PrintComments = xlPrintNoComments
            .PrintQuality = 600
            .CenterHorizontally = False
            .CenterVertically = False
            .Orientation = xlLandscape
            .Draft = False
            .PaperSize = xlPaperLetter
            .FirstPageNumber = xlAutomatic
            .order = xlDownThenOver
            .BlackAndWhite = False
            .Zoom = 100
            .PrintErrors = xlPrintErrorsDisplayed
            .OddAndEvenPagesHeaderFooter = False
            .DifferentFirstPageHeaderFooter = False
            .ScaleWithDocHeaderFooter = True
            .AlignMarginsHeaderFooter = True
        End With
        If Val(Application.Version) & gt = 14 Then Application.PrintCommunication = True
        
        'set column widths
        Columns("A:A").ColumnWidth = 4.6
        Columns("B:B").ColumnWidth = 4
        Columns("C:C").ColumnWidth = 39.2
        Columns("D:D").ColumnWidth = 32.5
        Columns("E:E").ColumnWidth = 10
        Columns("F:F").ColumnWidth = 10
        Columns("G:G").ColumnWidth = 28.6
        Columns("H:H").ColumnWidth = 28.6
        
        Range("A1:H1").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark2
            .TintAndShade = -0.249977111117893
            .PatternTintAndShade = 0
        End With
        Range("A1:H1").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlDouble
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThick
        End With
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Range("A1") = "Code"
        Range("B1") = "Qty"
        Range("C1") = "Botanical Name"
        Range("D1") = "Common Name"
        Range("E1") = "Size"
        Range("F1") = "Condition"
        Range("H1") = "Notes"
        
        Range("A:B,D:H").Select
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Cells(2, 3).Select
        ScreenUpdating = True
    End Function

  2. #2
    Registered User
    Join Date
    11-14-2011
    Location
    fairfax, virginia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Pages Generated by macro are slow

    sorry, here's the rest of the code
    Sub SortCopyPaste()
    Dim wkbk1 As Workbook
        Set wkbk1 = ActiveWorkbook
        
        With ThisWorkbook.Sheets("Masterlist")
            .Range("B:B").AutoFilter Field:=2, Criteria1:="<>"
            '// Copy all rows except header
            .UsedRange.Offset(3).SpecialCells(xlCellTypeVisible).EntireRow.copy ActiveWorkbook.ActiveSheet.Cells(4, 1)
            '// Remove the autofilter
            .Range("B:B").AutoFilter Field:=2
        End With
        Call Sort
        Call header
        Call otherwork
        Call Notes
        
    End Sub
    Function Sort()
    Dim rowcount
    
    rowcount = ActiveSheet.UsedRange.Rows.Count
        ActiveWorkbook.ActiveSheet.Sort.SortFields.clear
        ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range( _
        (Cells(3, 7)), (Cells(rowcount, 7))), SortOn:=xlSortOnValues, order:=xlAscending, _
        CustomOrder:=("TREES, SHRUBS, PERENNIALS, VINES/BULBS"), _
        DataOption:=xlSortNormal
        With ActiveWorkbook.ActiveSheet.Sort
            .SetRange Range((Cells(3, 1)), (Cells(rowcount, 7)))
            .header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End Function
    Function otherwork()
    Dim myRow As Long
        Dim myCount As Long
        Dim myCol As Integer
    
        myCol = 7
        myCount = ActiveSheet.UsedRange.Rows.Count
        
        Cells(myCount - 3, 1) = "OTHER WORK"
        
        Range((Cells(myCount + 1, 1)), (Cells(myCount + 1, myCol))).Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlDouble
        Range((Cells(myCount + 1, 1)), (Cells(myCount + 1, myCol))).Borders(xlEdgeTop).LineStyle = XlLineStyle.xlDouble
            With Cells(myCount - 3, 1)
                .Font.Bold = True
            End With
            With Range(Cells(myCount + -3, 1), Cells(myCount + 3, 1))
                .HorizontalAlignment = xlLeft
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .MergeCells = False
            End With
    End Function
    Function header()
        Dim myRow As Long
        Dim myCount As Long
        Dim myCol As Integer
    
        myCol = 7
        myCount = ActiveSheet.UsedRange.Rows.Count
        
        'Cells(myCount + 1, 1) = "OTHER WORK"
        
        'Range((Cells(myCount + 1, 1)), (Cells(myCount + 1, myCol + 1))).Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlDouble
        'Range((Cells(myCount + 1, 1)), (Cells(myCount + 1, myCol + 1))).Borders(xlEdgeTop).LineStyle = XlLineStyle.xlDouble
        '    With Cells(myCount - 3, 1)
        '        .Font.Bold = True
        '    End With
        '    With Range(Cells(myCount + 1, 1), Cells(myCount + 3, 1))
        '        .HorizontalAlignment = xlLeft
        '        .VerticalAlignment = xlCenter
        '        .WrapText = False
       '         .Orientation = 0
       '         .AddIndent = False
       '         .IndentLevel = 0
       '         .ShrinkToFit = False
       '         .ReadingOrder = xlContext
       '         .MergeCells = False
        '    End With
        
        'first border
        Range("G4").Select
        Selection.copy
        Range("A3").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A3:H3").Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlDouble
        Range("A3:H3").Borders(xlEdgeTop).LineStyle = XlLineStyle.xlDouble
        
        For myRow = myCount - 1 To 4 Step -1
        If Cells(myRow, myCol).Value <> Cells(myRow + 1, myCol).Value Then
            Range((Cells(myRow + 1, myCol)), (Cells(myRow + 2, myCol))).EntireRow.Insert
            Cells(myRow + 3, myCol).Select
            Selection.copy
            Cells(myRow + 2, myCol - 6).Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
            Range((Cells(myRow + 2, 1)), (Cells(myRow + 2, myCol + 1))).Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlDouble
            Range((Cells(myRow + 2, 1)), (Cells(myRow + 2, myCol + 1))).Borders(xlEdgeTop).LineStyle = XlLineStyle.xlDouble
        End If
        Next myRow
        
        Call NullG
    End Function
    Function Notes()
    Dim notecount As Integer
        notecount = ActiveSheet.UsedRange.Rows.Count
        'MsgBox (notecount)
        Cells(notecount - 2, 1) = "NOTES"
        With Cells(notecount - 2, 1)
            .Font.Bold = True
        End With
        With Range(Cells(notecount - 2, 1), Cells(notecount + 8, 1))
            .HorizontalAlignment = xlLeft
            .VerticalAlignment = xlCenter
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Cells(notecount - 1, 1) = "1.*All plant material shall conform to the “American Standards for Nursery Stock”,   ANSI Z60.1-2004 by the American Nursery and Landscape Association."
        Cells(notecount, 1) = "2.*All planting installation and soil preparation shall conform to the “ Landscape Specification Guidelines for Baltimore Washington Metropolitan Area”,"
        Cells(notecount + 1, 1) = "     most recent edition by the Landscape Contractors Association MD DC VA."
        Cells(notecount + 2, 2) = "3.*Contractor shall verify locations of all underground utilities within work areas and be"
        Cells(notecount + 3, 1) = "     responsible for their protection.  Call MISS UTILITY (1 800 257-7777) before installation commences."
        Cells(notecount + 4, 1) = "4. Planting plan provides general layout only.  Specific planting layout shall be directed by LA."
        Cells(notecount + 5, 1) = "5.*All plant substitutions to be approved by LA."
        Cells(notecount + 6, 1) = "6.*Contractor responsible for amending planting bed according to soil report or at LA direction."
        Cells(notecount + 7, 1) = "7.*Plant quantities to be verified by Contractor from planting plan.  Any discrepancies should be brought to the attention of the Landscape LA for verification."
    End Function
    
    Function NullG()
        ActiveSheet.Columns("G:G").Delete
    End Function

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1