Andy,

Try this:

Sub paste_n_times()
Dim copy_range As Range
Dim n As Long

Set copy_range = Range("A1:C5")
n = 3 'replace with your calc

With copy_range
.Copy
With .Resize(.Rows.Count, .Columns.Count * (n + 1))
.PasteSpecial Paste:=xlPasteFormulas
.PasteSpecial Paste:=xlPasteFormats
End With
End With
End Sub


hth,

Doug


"AndyOD" wrote in message
...
I am trying to set up asheet which will dynamically copy & paste formatted
cells a number of times. However this process will change depending on the
results of a separate calculation;

So far I can calculate the number of iterations and display the result in
a
cell, I now need to select the range of cells to be copied e.g. range
("A1:C5") and copy this range 'n' times adjacent to my originating block.

If I look at the code from a recorded macro the code reads as follows:
--
range("A1:C5").Select
Selection.Copy
range("D1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
range("G1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

How can I refer to my calculated result in order to set the macro to
automatically PasteSpecial 'n' number of times?
--
Cheers,

Andy