Copy and Paste Loop Error

[ShuStar]

Hi Gurus,

Can anyone help with below?

The task is to do a loop, starting on cell F4 in Extract sheet and do the following actions:
-Copy the cell in F4 in Extract sheet F4 and paste this into Test Sheet B2
-Then go back to Extract sheet F4 and paste this into Test Sheet B2
-Continue this loop based on the CountA formula in Extract sheet F1 (ie if F1 value was 20, it will do this copy and paste through to F4, F5, F6....F23)

I have the below code but its not working.. think i haven't declared something or missed it...


Dim i As Long
Dim CountinF1 As Integer
CountinF1 = Sheets("Extract").Range("F1").Value


For i = 1 To CountinF1
'start from F3 and then move onto F4,F5, F6 until end of CountinF1

Sheets("Extract").Range("F4").Select 'after one loop it gets stuck here with a run time error
Selection.Copy
Sheets("Test Sheet").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues

Next i

 

[Fluff]

If F1 is 20 do you want to copy F4 to B2 twenty time, or F4 To B2, then F5 to B2, or maybe F4 to B2, F5 to B3 etc?

 

[ShuStar]

No sorry - just copy once and then move f2 to b2, then f3 to b2 and so on.

I guess I meant the looping should go down to F23 based on F1 being 20.

 

[Fluff]

Ok, how about

Dim i As Long
Dim CountinF1 As Long

With Sheets("Extract")
   CountinF1 = .Range("F1").Value
   For i = 1 To CountinF1
      Sheets("Test Sheet").Range("B2").Value = .Range("F" & i + 3).Value
   Next i
End With

 

[ShuStar]

Yes, great! This is doing it as expected

 

[Fluff]

Glad to help & thanks for the feedback.