Copy & paste ranges dependant on results from another calculation

[Guest]

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?

 

[Guest]

Try this.

Sub Test()
Dim n%, i%

'Assuming the number of times the pasting is to be done is in Cell A6
n = Cells(6, 1).Value

If n > 84 Then n = 84

Range("A1:C5").Select
Selection.Copy

For i = 1 To n
Cells(1, 1 + i * 3).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Next i

End Sub

 

[Guest]

Alok,

Thank you for this. It is exactly what I was looking for. Brilliant!

 

[Doug Glancy]

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