Loop same formula for different columns?

[JD]

Hi,

I have a macro, part of which copies from one worksheet and pastes into
another. The problem is that not only does it have to follow the same
process (copy position moves nine columns, but the destination column
moves two columns and then every fourth change, changes four) and I
know it is poor coding to simply write it out fifteen times.

An extract of the draft is provided below to give an indication:
//--->
Sheets("Sheet1").Range("J10:J100"). _
SpecialCells(xlCellTypeFormulas, 23).Copy
Sheets("Sheet2").Range("F4").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Sheets("Sheet1").Range("S10:S100"). _
SpecialCells(xlCellTypeFormulas, 23).Copy
Sheets("Sheet2").Range("H4").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Sheets("Sheet1").Range("AB10:AB100"). _
SpecialCells(xlCellTypeFormulas, 23).Copy
Sheets("Sheet2").Range("J4").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Sheets("Sheet1").Range("AK10:AK100"). _
SpecialCells(xlCellTypeFormulas, 23).Copy
Sheets("Sheet2").Range("N4").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
--->//
What I was hoping to do was change all this to a loop utilising
ActiveCell.Offset

Would this work and how should I do it?

 

[Tim Williams]

Untested:

************************************
dim rngCopy as range
dim x as integer

set rngCopy = Sheets("Sheet1").Range("J10:J100")
set rngPaste=Sheets("Sheet2").Range("F4")

for n=1 to 15
rngCopy.SpecialCells(xlCellTypeFormulas, 23).Copy
rngPaste.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
if n mod 4 = 0 then
set rngPaste=rngPase.offset(0,4)
else
set rngPaste=rngPase.offset(0,2)
end if

set rngCopy=rngCopy.offset(0,9)
next n
*********************************

 

[JD]

Untested:

************************************
dim rngCopy as range
dim x as integer

set rngCopy = Sheets("Sheet1").Range("J10:J100")
set rngPaste=Sheets("Sheet2").Range("F4")

for n=1 to 15
rngCopy.SpecialCells(xlCellTypeFormulas, 23).Copy
rngPaste.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
if n mod 4 = 0 then
set rngPaste=rngPase.offset(0,4)
else
set rngPaste=rngPase.offset(0,2)
end if

set rngCopy=rngCopy.offset(0,9)
next n
*********************************

Cheers Tim, I understand that. It works, provided I make the obvious
changes:
if n mod 3 = 0 then (my error in explaining)
set rngPaste=rngPaste

Thanks for that. This really is a helpful ng.