Copying a range with a twist

[Mel Smith]

Hello folks,

I want to copy a range of cells (A1:AP165) 59 times. If that was all I wanted, then no problem. The issue I have is that cell A2 refers as follows: =Feeder!$A$2. When the range is copied for the first time, the reference needs to change to $A$3 and then $A$4 and so on.

I could change cell A2 to be =Feeder!A2 but how can I get the reference to increase by 1 on each copying of the range.

Many thanks for your help.

Mel

 

[Fluff]

Where do you want to copy it to?

 

[Mel Smith]

The next range will start at A166 and so on.

Mel

 

[Fluff]

Ok, how about

Sub MelSmith()
   Dim i As Long
   Range("A1:AP165").Copy Range("A166").Resize(165 * 9, 42)
   With Range("A:A").SpecialCells(xlFormulas).Areas
      For i = 2 To .Count
         .Item(i).Formula = "=Feeder!$A$" & i + 1
      Next i
   End With
End Sub

 

[Mel Smith]

Many thanks,Fluff
Mel

 

[Fluff]

You're welcome & thanks for the feedback