My macro lags

**POPSS** · 01-28-2025, 01:57 PM

My copy and paste macro sometimes take 15 to 20 seconds to run and bogs down it also gives me a clipboard error. It does work but is there a more efficient way to run this? I made this with recording the macro.

Range("A3:A85").Copy Range("CC3,CD3")
Range("E3:E85").Copy Range("CC169,CD169")
Range("I3:I85").Copy Range("CC252,CD252")
Range("M3:M85").Copy Range("CC335,CD335")
Range("Q3:Q85").Copy Range("CC418,CD418")
Range("U3:U85").Copy Range("CC501,CD501")
Range("Y3:Y85").Copy Range("CC584,CD584")
Range("AC3:AC85").Copy Range("CC667,CD667")
Range("AG3:AG85").Copy Range("CC750,CD750")
Range("AK3:AK85").Copy Range("CC833,CD833")
Range("AO3:AO85").Copy Range("CC916,CD916")
Range("AS3:AS85").Copy Range("CC999,CD999")
Range("AW3:AW85").Copy Range("CC1082,CD1082")
Range("BA3:BA85").Copy Range("CC1165,CD1165")
Range("BE3:BE85").Copy Range("CC1248,CD1248")
Range("BI3:BI85").Copy Range("CC1331,CD1331")
Range("BM3:BM85").Copy Range("CC1414,CD1414")
Range("BQ3:BQ85").Copy Range("CC1497,CD1497")
Range("BU3:BU85").Copy Range("CC1580,CD1580")
Range("BY3:BY85").Copy Range("CC1663,CD1663")
Range("B3:B85").Copy Range("CC1746")
Range("F3:F85").Copy Range("CC1829")
Range("J3:J85").Copy Range("CC1912")
Range("N3:N85").Copy Range("CC1995")
Range("R3:R85").Copy Range("CC2078")
Range("V3:V85").Copy Range("CC2161")
Range("Z3:Z85").Copy Range("CC2244")
Range("AD3:AD85").Copy Range("CC2327")
Range("AH3:AH85").Copy Range("CC2410")
Range("AL3:AL85").Copy Range("CC2493")
Range("AP3:AP85").Copy Range("CC2576")
Range("AT3:AT85").Copy Range("CC2659")
Range("AX3:AX85").Copy Range("CC2742")
Range("BB3:BB85").Copy Range("CC2825")
Range("BF3:BF85").Copy Range("CC2908")
Range("BJ3:BJ85").Copy Range("CC2991")
Range("BN3:BN85").Copy Range("CC3074")
Range("BR3:BR85").Copy Range("CC3157")
Range("BV3:BV85").Copy Range("CC3240")
Range("BZ3:BZ85").Copy Range("CC3323")
Range("C3:C85").Copy Range("CD1746")
Range("G3:G85").Copy Range("CD1829")
Range("K3:K85").Copy Range("CD1912")
Range("O3:O85").Copy Range("CD1995")
Range("S3:S85").Copy Range("CD2078")
Range("W3:W85").Copy Range("CD2161")
Range("AA3:AA85").Copy Range("CD2244")
Range("AE3:AE85").Copy Range("CD2327")
Range("AI3:AI85").Copy Range("CD2410")
Range("AM3:AM85").Copy Range("CD2493")
Range("AQ3:AQ85").Copy Range("CD2576")
Range("AU3:AU85").Copy Range("CD2659")
Range("AY3:AY85").Copy Range("CD2742")
Range("BC3:BC85").Copy Range("CD2825")
Range("BG3:BG85").Copy Range("CD2908")
Range("BK3:BK85").Copy Range("CD2991")
Range("BO3:BO85").Copy Range("CD3074")
Range("BS3:BS85").Copy Range("CD3157")
Range("BW3:BW85").Copy Range("CD3240")
Range("CA3:CA85").Copy Range("CD3323")
Range("D3:D85").Copy Range("CE3")
Range("H3:H85").Copy Range("CE86")
Range("L3:L85").Copy Range("CE169")
Range("P3:P85").Copy Range("CE252")
Range("T3:T85").Copy Range("CE335")
Range("X3:X85").Copy Range("CE418")
Range("AB3:AB85").Copy Range("CE501")
Range("AF3:AF85").Copy Range("CE584")
Range("AJ3:AJ85").Copy Range("CE667")
Range("AN3:AN85").Copy Range("CE750")
Range("AR3:AR85").Copy Range("CE833")
Range("AV3:AV85").Copy Range("CE916")
Range("AZ3:AZ85").Copy Range("CE999")
Range("BD3:BD85").Copy Range("CE1082")
Range("BH3:BH85").Copy Range("CE1165")
Range("BL3:BL85").Copy Range("CE1248")
Range("BP3:BP85").Copy Range("CE1331")
Range("BT3:BT85").Copy Range("CE1414")
Range("BX3:BX85").Copy Range("CE1497")
Range("CB3:CB85").Copy Range("CE1580")

Thanks in advance

**MikeVol** · 01-28-2025, 02:58 PM

Hi@POPSS! Can you attach a sample file to customize the code?

**TMS** · 01-28-2025, 06:46 PM

Use:

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

' your code

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True

**POPSS** · 01-29-2025, 12:01 AM

Much faster, Thank you Trevor

**TMS** · 01-28-2025, 06:48 PM

Edit: deleted. Misread the ranges.

**POPSS** · 01-28-2025, 07:28 PM

Here is the full macro that copies all columns into the three columns and then highlights any matches left of the dash. It works but sometimes slow. I was wondering if there was a better way to copy and paste that would stop the clipboard error and speed up the process
Thanks in advance

Range("CC3:CE3457").Select
    Selection.ClearContents
    Range("CC3:CE3300").Select
    Selection.ClearComments
     Range("A3:A85").Copy Range("CC3,CD3")
    Range("E3:E85").Copy Range("CC169,CD169")
    Range("I3:I85").Copy Range("CC252,CD252")
    Range("M3:M85").Copy Range("CC335,CD335")
    Range("Q3:Q85").Copy Range("CC418,CD418")
    Range("U3:U85").Copy Range("CC501,CD501")
    Range("Y3:Y85").Copy Range("CC584,CD584")
    Range("AC3:AC85").Copy Range("CC667,CD667")
    Range("AG3:AG85").Copy Range("CC750,CD750")
    Range("AK3:AK85").Copy Range("CC833,CD833")
    Range("AO3:AO85").Copy Range("CC916,CD916")
    Range("AS3:AS85").Copy Range("CC999,CD999")
    Range("AW3:AW85").Copy Range("CC1082,CD1082")
    Range("BA3:BA85").Copy Range("CC1165,CD1165")
    Range("BE3:BE85").Copy Range("CC1248,CD1248")
    Range("BI3:BI85").Copy Range("CC1331,CD1331")
    Range("BM3:BM85").Copy Range("CC1414,CD1414")
    Range("BQ3:BQ85").Copy Range("CC1497,CD1497")
    Range("BU3:BU85").Copy Range("CC1580,CD1580")
    Range("BY3:BY85").Copy Range("CC1663,CD1663")
    Range("B3:B85").Copy Range("CC1746")
    Range("F3:F85").Copy Range("CC1829")
    Range("J3:J85").Copy Range("CC1912")
    Range("N3:N85").Copy Range("CC1995")
    Range("R3:R85").Copy Range("CC2078")
    Range("V3:V85").Copy Range("CC2161")
    Range("Z3:Z85").Copy Range("CC2244")
    Range("AD3:AD85").Copy Range("CC2327")
    Range("AH3:AH85").Copy Range("CC2410")
    Range("AL3:AL85").Copy Range("CC2493")
    Range("AP3:AP85").Copy Range("CC2576")
    Range("AT3:AT85").Copy Range("CC2659")
    Range("AX3:AX85").Copy Range("CC2742")
    Range("BB3:BB85").Copy Range("CC2825")
    Range("BF3:BF85").Copy Range("CC2908")
    Range("BJ3:BJ85").Copy Range("CC2991")
    Range("BN3:BN85").Copy Range("CC3074")
    Range("BR3:BR85").Copy Range("CC3157")
    Range("BV3:BV85").Copy Range("CC3240")
    Range("BZ3:BZ85").Copy Range("CC3323")
    Range("C3:C85").Copy Range("CD1746")
    Range("G3:G85").Copy Range("CD1829")
    Range("K3:K85").Copy Range("CD1912")
    Range("O3:O85").Copy Range("CD1995")
    Range("S3:S85").Copy Range("CD2078")
    Range("W3:W85").Copy Range("CD2161")
    Range("AA3:AA85").Copy Range("CD2244")
    Range("AE3:AE85").Copy Range("CD2327")
    Range("AI3:AI85").Copy Range("CD2410")
    Range("AM3:AM85").Copy Range("CD2493")
    Range("AQ3:AQ85").Copy Range("CD2576")
    Range("AU3:AU85").Copy Range("CD2659")
    Range("AY3:AY85").Copy Range("CD2742")
    Range("BC3:BC85").Copy Range("CD2825")
    Range("BG3:BG85").Copy Range("CD2908")
    Range("BK3:BK85").Copy Range("CD2991")
    Range("BO3:BO85").Copy Range("CD3074")
    Range("BS3:BS85").Copy Range("CD3157")
    Range("BW3:BW85").Copy Range("CD3240")
    Range("CA3:CA85").Copy Range("CD3323")
    Range("D3:D85").Copy Range("CE3")
    Range("H3:H85").Copy Range("CE86")
    Range("L3:L85").Copy Range("CE169")
    Range("P3:P85").Copy Range("CE252")
    Range("T3:T85").Copy Range("CE335")
    Range("X3:X85").Copy Range("CE418")
    Range("AB3:AB85").Copy Range("CE501")
    Range("AF3:AF85").Copy Range("CE584")
    Range("AJ3:AJ85").Copy Range("CE667")
    Range("AN3:AN85").Copy Range("CE750")
    Range("AR3:AR85").Copy Range("CE833")
    Range("AV3:AV85").Copy Range("CE916")
    Range("AZ3:AZ85").Copy Range("CE999")
    Range("BD3:BD85").Copy Range("CE1082")
    Range("BH3:BH85").Copy Range("CE1165")
    Range("BL3:BL85").Copy Range("CE1248")
    Range("BP3:BP85").Copy Range("CE1331")
    Range("BT3:BT85").Copy Range("CE1414")
    Range("BX3:BX85").Copy Range("CE1497")
    Range("CB3:CB85").Copy Range("CE1580")
    Range("CC3:CE3362").Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
       Range("CC3:CC3362").Select
    ActiveWorkbook.Worksheets("Sheet1").SORT.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").SORT.SortFields.Add2 Key:=Range( _
        "CC3:CC3362"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").SORT
        .SetRange Range("CC3:CC3362")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
       Range("CD3:CD3362").Select
    ActiveWorkbook.Worksheets("Sheet1").SORT.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").SORT.SortFields.Add2 Key:=Range( _
        "CD3:CD3362"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").SORT
        .SetRange Range("CD3:CD3362")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
        Range("CE3:CE3362").Select
    ActiveWorkbook.Worksheets("Sheet1").SORT.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").SORT.SortFields.Add2 Key:=Range( _
        "CE3:CE3362"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").SORT
        .SetRange Range("CE3:CE3362")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
        Range("CC3:CC1000").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIF($CC$3:$CC$1000,LEFT(CC3,FIND(""-"",CC3)-1)&""-*"")>1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
       End With
    Range("CD3:CD1000").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIF($CD$3:$CD$1000,LEFT(CD3,FIND(""-"",CD3)-1)&""-*"")>1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
        End With
    Range("CE3:CE1000").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIF($CE$3:$CE$1000,LEFT(CE3,FIND(""-"",CE3)-1)&""-*"")>1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
        End With
        Range("cf3").Select
        
End Sub

**6StringJazzer** · 01-28-2025, 11:55 PM

Originally Posted by POPSS

Here is the full macro

It is very difficult to read this code. Please edit your first and also most recent post to add code tags. I assume you have seen this done sometime in the last 6 years.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however we recommend that you include code tags around your code.

Please take a moment to add the tags. Posting code between [code] [/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Guideline #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here.

**MikeVol** · 01-29-2025, 03:23 AM

Hello @POPSS!
Optimized code for post #1:

Option Explicit

Sub TestCopyRNG()
    Dim srcRanges As Variant, destRanges As Variant
    Dim i           As Integer
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    srcRanges = Array("A3:A85", "E3:E85", "I3:I85", "M3:M85", "Q3:Q85", "U3:U85", "Y3:Y85", "AC3:AC85", "AG3:AG85", "AK3:AK85", _
            "AO3:AO85", "AS3:AS85", "AW3:AW85", "BA3:BA85", "BE3:BE85", "BI3:BI85", "BM3:BM85", "BQ3:BQ85", "BU3:BU85", "BY3:BY85")
    destRanges = Array("CC3,CD3", "CC169,CD169", "CC252,CD252", "CC335,CD335", "CC418,CD418", "CC501,CD501", "CC584,CD584", "CC667,CD667", _
            "CC750,CD750", "CC833,CD833", "CC916,CD916", "CC999,CD999", "CC1082,CD1082", "CC1165,CD1165", "CC1248,CD1248", "CC1331,CD1331", _
            "CC1414,CD1414", "CC1497,CD1497", "CC1580,CD1580", "CC1663,CD1663")

    For i = LBound(srcRanges) To UBound(srcRanges)
        Range(srcRanges(i)).Copy Range(destRanges(i))
    Next i

    srcRanges = Array("B3:B85", "F3:F85", "J3:J85", "N3:N85", "R3:R85", "V3:V85", "Z3:Z85", "AD3:AD85", "AH3:AH85", "AL3:AL85", _
            "AP3:AP85", "AT3:AT85", "AX3:AX85", "BB3:BB85", "BF3:BF85", "BJ3:BJ85", "BN3:BN85", "BR3:BR85", "BV3:BV85", "BZ3:BZ85")
    destRanges = Array("CC1746", "CC1829", "CC1912", "CC1995", "CC2078", "CC2161", "CC2244", "CC2327", "CC2410", "CC2493", _
            "CC2576", "CC2659", "CC2742", "CC2825", "CC2908", "CC2991", "CC3074", "CC3157", "CC3240", "CC3323")

    For i = LBound(srcRanges) To UBound(srcRanges)
        Range(srcRanges(i)).Copy Range(destRanges(i))
    Next i

    srcRanges = Array("C3:C85", "G3:G85", "K3:K85", "O3:O85", "S3:S85", "W3:W85", "AA3:AA85", "AE3:AE85", "AI3:AI85", "AM3:AM85", _
            "AQ3:AQ85", "AU3:AU85", "AY3:AY85", "BC3:BC85", "BG3:BG85", "BK3:BK85", "BO3:BO85", "BS3:BS85", "BW3:BW85", "CA3:CA85")
    destRanges = Array("CD1746", "CD1829", "CD1912", "CD1995", "CD2078", "CD2161", "CD2244", "CD2327", "CD2410", "CD2493", _
            "CD2576", "CD2659", "CD2742", "CD2825", "CD2908", "CD2991", "CD3074", "CD3157", "CD3240", "CD3323")

    For i = LBound(srcRanges) To UBound(srcRanges)
        Range(srcRanges(i)).Copy Range(destRanges(i))
    Next i

    srcRanges = Array("D3:D85", "H3:H85", "L3:L85", "P3:P85", "T3:T85", "X3:X85", "AB3:AB85", "AF3:AF85", "AJ3:AJ85", "AN3:AN85", _
            "AR3:AR85", "AV3:AV85", "AZ3:AZ85", "BD3:BD85", "BH3:BH85", "BL3:BL85", "BP3:BP85", "BT3:BT85", "BX3:BX85", "CB3:CB85")
    destRanges = Array("CE3", "CE86", "CE169", "CE252", "CE335", "CE418", "CE501", "CE584", "CE667", "CE750", _
            "CE833", "CE916", "CE999", "CE1082", "CE1165", "CE1248", "CE1331", "CE1414", "CE1497", "CE1580")

    For i = LBound(srcRanges) To UBound(srcRanges)
        Range(srcRanges(i)).Copy Range(destRanges(i))
    Next i

    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Regarding your second code from post #5, I had problems with Conditional Formatting. Maybe another user can add to my code. Good luck.

**hungt** · 01-29-2025, 04:37 AM

Sub test()
Dim i As Long, j As Long, k As Long, startRow As Long, currCol As Long, srcArr(), resArr()
    srcArr = Worksheets("Sheet1").Range("A3:CB85").Value
    ReDim resArr(1 To 3403, 1 To 3)
'    Copy A3:A85
    For i = 1 To 83
        resArr(i, 1) = srcArr(i, 1)
        resArr(i, 2) = srcArr(i, 1)
    Next i
'    copy E3:E85, I3:I85, ...
    For j = 1 To 19
        startRow = 166 + (j - 1) * 83
        currCol = 1 + j * 4
        For i = 1 To 83
            resArr(startRow + i, 1) = srcArr(i, currCol)
            resArr(startRow + i, 2) = srcArr(i, currCol)
        Next i
    Next j
'    copy
    For k = 1 To 2
        For j = 1 To 20
            startRow = 1743 + (j - 1) * 83
            currCol = k + 1 + (j - 1) * 4
            For i = 1 To 83
                resArr(startRow + i, k) = srcArr(i, currCol)
            Next i
        Next j
    Next k
    For j = 1 To 20
        startRow = (j - 1) * 83
        currCol = j * 4
        For i = 1 To 83
            resArr(startRow + i, 3) = srcArr(i, currCol)
        Next i
    Next j
    Worksheets("Sheet1").Range("CC3").Resize(UBound(resArr, 1), UBound(resArr, 2)).Value = resArr
End Sub

**TMS** · 01-29-2025, 11:28 AM

Thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

**POPSS** · 01-29-2025, 04:51 PM

Thanks for the help. I am very much a newbie and don't understand code tags and how to insert them. All my macros were made by recording. I have 5 macros in one module. I think I narrowed the issue down to when I run the print macro the total macro bogs down afterward. I originally put in the code you gave me and it sped up but after the print runs it slows back down. Would it be helpful if I copy and paste all the macros in the module below? I really don't have a clue what I'm doing but appreciate all your help.

**6StringJazzer** · 01-29-2025, 07:20 PM

Originally Posted by POPSS

I am very much a newbie and don't understand code tags and how to insert them.

You joined the forum 6 years ago which isn't new. I suggest you review our rules and guidelines.

There are two ways to add code tags. One is to select the code part of the text so it is highlighted, then press the "#" button in the edit controls. The other is to simply type in the tags:

[code]
' your code goes here
[/code]

Why use code tags? Code tags:

1. Are recommended by our guidelines ()
2. Preserve spacing used to show code structure (otherwise leading spaces and repeated spaces are removed)
3. Add scroll bars to navigate code with long lines or a lot of lines

**TMS** · 01-29-2025, 06:26 PM

... and don't understand code tags and how to insert them.

See post #6.

**POPSS** · 01-30-2025, 08:11 AM

I hope I added the proper code tags to this print macro. After running it my other total macro runs 5 times slower. I'm not sure what the correlation is.

Sub PRINTHEAVY2()
'
' PRINTHEAVY2 Macro
'

'
    Range("CC3:CC66").Copy Range("CH3")
    Range("CC67:CC130").Copy Range("CI3")
    Range("CC131:CC194").Copy Range("CJ3")
    Range("CC195:CC258").Copy Range("CK3")
    Range("CC259:CC322").Copy Range("CL3")
    Range("CC323:CC386").Copy Range("CM3")
    Range("CC387:CC450").Copy Range("CN3")
    Range("CD3:CD66").Copy Range("CQ3")
    Range("CD67:CD130").Copy Range("CR3")
    Range("CD131:CD194").Copy Range("CS3")
    Range("CD195:CD258").Copy Range("CT3")
    Range("CD259:CD322").Copy Range("CU3")
    Range("CD323:CD386").Copy Range("CV3")
    Range("CD387:CD450").Copy Range("CW3")
    Range("CD451:CD514").Copy Range("CX3")
    Range("CD515:CD578").Copy Range("CY3")
    Range("CE3:CE66").Copy Range("CO3")
    Range("CE67:CE130").Copy Range("CP3")
    
   Range("CH3:CN66").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    ActiveWindow.SmallScroll Down:=-26
    Range("CO3:CP66").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    ActiveWindow.SmallScroll Down:=-29
    Range("CQ3:CY66").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    ActiveWindow.SmallScroll Down:=-29
    Range("CH2:CY66").Select
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.7)
        .RightMargin = Application.InchesToPoints(0.7)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = True
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
    Selection.PrintOut Copies:=1, Collate:=True
    
    
    
    
    
    
    
End Sub

**romperstomper** · 01-30-2025, 08:18 AM

It might help to add:

activesheet.displaypagebreaks = false

to the start of your total macro.

**POPSS** · 01-30-2025, 06:54 PM

Thank you Romper, I added that to my total macro and there is no lag after printing. It runs under 4 seconds every time. I tried adding that to my print macro also but that one still runs slow. It takes 40 seconds to execute before sending to the printer. Is there something I can change to speed that up?

Sub PRINTHEAVY2()
'
' PRINTHEAVY2 Macro
'

'   ActiveSheet.DisplayPageBreaks = False
    Range("CC3:CC66").Copy Range("CH3")
    Range("CC67:CC130").Copy Range("CI3")
    Range("CC131:CC194").Copy Range("CJ3")
    Range("CC195:CC258").Copy Range("CK3")
    Range("CC259:CC322").Copy Range("CL3")
    Range("CC323:CC386").Copy Range("CM3")
    Range("CC387:CC450").Copy Range("CN3")
    Range("CD3:CD66").Copy Range("CQ3")
    Range("CD67:CD130").Copy Range("CR3")
    Range("CD131:CD194").Copy Range("CS3")
    Range("CD195:CD258").Copy Range("CT3")
    Range("CD259:CD322").Copy Range("CU3")
    Range("CD323:CD386").Copy Range("CV3")
    Range("CD387:CD450").Copy Range("CW3")
    Range("CD451:CD514").Copy Range("CX3")
    Range("CD515:CD578").Copy Range("CY3")
    Range("CE3:CE66").Copy Range("CO3")
    Range("CE67:CE130").Copy Range("CP3")

   Range("CH3:CN66").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    ActiveWindow.SmallScroll Down:=-26
    Range("CO3:CP66").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    ActiveWindow.SmallScroll Down:=-29
    Range("CQ3:CY66").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    ActiveWindow.SmallScroll Down:=-29
    Range("CH2:CY66").Select
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.7)
        .RightMargin = Application.InchesToPoints(0.7)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = True
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
    Selection.PrintOut Copies:=1, Collate:=True

End Sub

**POPSS** · 01-30-2025, 07:54 PM

Thank you Romper, I added that to my total macro and there is no lag after printing. It runs under 4 seconds every time. I tried adding that to my print macro also but that one still runs slow. It takes 40 seconds to execute before sending to the printer. Is there something I can change to speed that up?

Sub PRINTHEAVY2()
'
' PRINTHEAVY2 Macro
'

'   ActiveSheet.DisplayPageBreaks = False
    Range("CC3:CC66").Copy Range("CH3")
    Range("CC67:CC130").Copy Range("CI3")
    Range("CC131:CC194").Copy Range("CJ3")
    Range("CC195:CC258").Copy Range("CK3")
    Range("CC259:CC322").Copy Range("CL3")
    Range("CC323:CC386").Copy Range("CM3")
    Range("CC387:CC450").Copy Range("CN3")
    Range("CD3:CD66").Copy Range("CQ3")
    Range("CD67:CD130").Copy Range("CR3")
    Range("CD131:CD194").Copy Range("CS3")
    Range("CD195:CD258").Copy Range("CT3")
    Range("CD259:CD322").Copy Range("CU3")
    Range("CD323:CD386").Copy Range("CV3")
    Range("CD387:CD450").Copy Range("CW3")
    Range("CD451:CD514").Copy Range("CX3")
    Range("CD515:CD578").Copy Range("CY3")
    Range("CE3:CE66").Copy Range("CO3")
    Range("CE67:CE130").Copy Range("CP3")

   Range("CH3:CN66").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    ActiveWindow.SmallScroll Down:=-26
    Range("CO3:CP66").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    ActiveWindow.SmallScroll Down:=-29
    Range("CQ3:CY66").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    ActiveWindow.SmallScroll Down:=-29
    Range("CH2:CY66").Select
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.7)
        .RightMargin = Application.InchesToPoints(0.7)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = True
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
    Selection.PrintOut Copies:=1, Collate:=True

End Sub

**romperstomper** · 01-31-2025, 06:18 AM

You should add the code from Trevor's post #3 for starters. I'm also not sure there's any point there to turning printcommunication off then on then off then on all in the same routine. Just turn it off, do all the pagesetup work (which is notoriously slow), then turn it back on.

**POPSS** · 01-31-2025, 09:35 AM

I added Trevors code and turned off the unnecessary print commands and cut down the execution time to less than 25%. I think everything is running smoothly now. I want to thank you all for the help that I would have happily paid a grand for! Hopefully I don't need to come back for another six years!.

My macro lags

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

My macro lags

Re: My macro lags

Re: My macro lags

Re: My macro lags

Re: My macro lags

Re: My macro lags

Re: My macro lags

Re: My macro lags

Re: My macro lags

Re: My macro lags

Re: My macro lags

Re: My macro lags

Re: My macro lags

Re: My macro lags

Re: My macro lags

Re: My macro lags

Re: My macro lags

Re: My macro lags

Re: My macro lags

Thread Information

Users Browsing this Thread

Similar Threads

Lags and Database Errors

Excel Workbook Lags

Listbox lags when I scroll up and down

Performance issues, program lags before executing

Autosave lags in Excel 2007

Workbook w/ macros lags or freezes when opened

Send to Mail Recipient (as attachment) Lags

Bookmarks

Bookmarks

Posting Permissions