Copy destination:= is extremely slow with small range???

vanowm

New Member
Joined
Jun 22, 2019
Messages
11
Hello.

I have a small "template" form (A1:L19) that I need to copy multiple times to a different worksheet for printing.
It takes over half a minute for excel to copy it 20 times.
The "template" contains joined cells, colors, different formattings, row heights, etc.

Code:
Sub test()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    Dim D As Worksheet
    Dim S As Worksheet
    Dim SR As Range
    Dim DR As Range
    Set S = Sheets("Base")      'source worksheet
    Set D = Sheets("Print")     'destination worksheet
    Set SR = S.Range("A1:L19")  'source range
    
    D.Cells.Delete              'clear destination worksheet
    
    colsCount = SR.Columns.Count
    rowsCount = SR.Rows.Count
    numCopies = 20              'number of copies
    curRow = 1
    
    For num = 1 To numCopies
        Set DR = D.Range(D.Cells(curRow, 1), D.Cells(curRow + rowsCount, 1)) 'destination range
        
        SR.Copy Destination:=DR                                              'copy data
        
        For n = 1 To rowsCount
            DR.Rows(n).RowHeight = SR.Rows(n).RowHeight                      'copy rows height
        Next n
        curRow = num * rowsCount + 1
    Next num
    
    For n = 1 To colsCount
        DR.Columns(n).ColumnWidth = SR.Columns(n).ColumnWidth                'copy columns width
    Next n
    
    Application.CutCopyMode = False
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

without SR.Copy Destination:=DR line it takes maybe 5 seconds (still a bit too long for what's it's doing)

Here is a template I'm testing it with:
https://drive.google.com/file/d/1EkIXVrOR05KfLs4myWCHsOHbomWaEB53/view

Is there anything can be done to optimize and speed up this process?

Thank you.

Windows 10 x64 Pro, MS Office Pro 2019

P.S.
I've posted exact same question on another forum, and someone replied that their Excel 2013 finish this routine in half a second...maybe something in Excel 2019 messed up?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
It's almost instant for me....so check for corruption of one of the tabs....
 
Upvote 0
I've posted exact same question on another forum, and someone replied that their Excel 2013 finish this routine in half a second...maybe something in Excel 2019 messed up?

I have excel 2019 and it took less than a second for me
Download the test link you sent and try it in a new workbook
 
Last edited:
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,725
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top