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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
It's almost instant for me....so check for corruption of one of the tabs....
 

Twollaston

Board Regular
Joined
May 24, 2019
Messages
235
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:

Forum statistics

Threads
1,148,108
Messages
5,744,876
Members
423,907
Latest member
zerocool88

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
Top