constructing a copy-paste loop that skips rows
 

Hello. Yet another newbie question here.

I am trying to write a macro, starting with A4, copies the text in every
44th row and pastes the text into another worksheet. It should do this for
a user defined number of times. The first cell of interest is A4, the next
would then be A48, etc...

I can't figure out how to construct the loop. I have tried it using Step
(is this right?) and also using variables and Offset. I have gotten so
confused that I don't know what I am doing at this point!

Here is the mess that I have at the moment:

Sub test2()
Dim StartValue As Integer
Dim n As Integer

n = CInt(InputBox("Enter the number of inputs: "))
StartValue = 4
For Count = 1 To n Step 44
ActiveCell.Offset(StartValue, 0).Select
Selection.Copy
Sheets("Sheet2").Activate
ActiveCell(Count, 0).Select
ActiveSheet.Paste
Sheets("Sheet1").Activate
Next Count
End Sub

Any help would be much appreciated!

Thanks,

H.M.

constructing a copy-paste loop that skips rows
 

Hi,

with the code as stated, the loop will terminate when
count becomes larger than n, and count will increase with
44 (the step) for each iteration of the loop.

Since you want to iterate the loop n times, it might be
easier to simply loop with step one and calculate the row
index at the start of each iteration. E.g.,

For i=1 to n
rowIndex = 44*n
...
Next i

Remember KISS: Keep It Simple Stupid! (In hardware design
also means Keep It Strictly Synchronous.) That at least
helps me every day!

Dag :) -- DagOystein.Johansen AT sage.com

-----Original Message-----
Hello. Yet another newbie question here.

I am trying to write a macro, starting with A4, copies
the text in every
44th row and pastes the text into another worksheet. It
should do this for
a user defined number of times. The first cell of
interest is A4, the next
would then be A48, etc...

I can't figure out how to construct the loop. I have
tried it using Step
(is this right?) and also using variables and Offset. I
have gotten so
confused that I don't know what I am doing at this point!

Here is the mess that I have at the moment:

Sub test2()
Dim StartValue As Integer
Dim n As Integer

n = CInt(InputBox("Enter the number of inputs: "))
StartValue = 4
For Count = 1 To n Step 44
ActiveCell.Offset(StartValue, 0).Select
Selection.Copy
Sheets("Sheet2").Activate
ActiveCell(Count, 0).Select
ActiveSheet.Paste
Sheets("Sheet1").Activate
Next Count
End Sub

Any help would be much appreciated!

Thanks,

H.M.


.