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

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


"AndyOD" wrote:

> 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

Alok,

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

Andy


"Alok" wrote:

> 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
>
>
> "AndyOD" wrote:
>
> > 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

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" <(E-Mail Removed)> wrote in message
news:C5013E52-58E0-47EB-B681-(E-Mail Removed)...
>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