How to make this VBA loop run quicker?

[treazon]

Hi All

I've recorded a macro then looped it, but it runs so slowly (takes around 45 minutes to complete). I've got a feeling it's so slow because it has to copy and pastes 12 images (barcodes) 537 times, but is there any way of using different syntax to make it quicker?

Here's the code:

Sub COPY_INSERT()'
' COPY_INSERT Macro
'


'
    Let x = 0
    Do While x < 537


    ActiveCell.Offset(1).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Offset(-1, 0).Select
    Selection.EntireRow.Select
    Selection.Copy
    Selection.End(xlDown).Select
    ActiveCell.Offset(1).Select
    Selection.Insert Shift:=xlDown
    
    x = x + 1
    Loop


    
End Sub

Thanks in advance!

 

[RoryA]

Is that all it's supposed to do - copy 12 images repeatedly?

 

[treazon]

Is that all it's supposed to do - copy 12 images repeatedly?

It's quite hard to explain, but I'll try...

There is a list of 537 different stores with different purchase order numbers for each. The aim is to insert the same 13 rows (including headers) below each of these rows. These 13 rows contain images (barcodes) as well as formulas that rely on the store / purchase order row above (one of the cells from each of the rows returns the purchase order number for the store).

So far, as long as I have the 13 rows I require to be copied below the first store / purchase order row, as well as the active cell being the top-left cell in the 13 rows, the code selects the 13 rows, copies them, goes to the cell below the selection, goes down 1 more and inserts the copied rows above this row (and repeats the required amount of times).

It just takes ages!