Selecting A Range Of Cells Relative To The Current Cell

[Samuels90]

Hey everyone,

I want to select a range of cell relative to A2 then copy and paste that range 12 times.
See my coding below. I have been getting a Warning box titled "Microsoft Visual Basic"
(X) 400.

Can anyone see what I am not doing correctly.

Thank you.
[CODE]Sub mycopytry()

Dim check As Integer

For check = 1 To 12
Range(ActiveCel l.Offset(29, 0), ActiveCell.Offs et(0, 0)).Select
Selection.Copy
ActiveCell.Offs et(1, 0).Select
ActiveSheet.Pas te
Next

End Sub
CODE]

[kadghar]

Hey everyone,

I want to select a range of cell relative to A2 then copy and paste that range 12 times.
See my coding below. I have been getting a Warning box titled "Microsoft Visual Basic"
(X) 400.

Can anyone see what I am not doing correctly.

Thank you.
[CODE]Sub mycopytry()

Dim check As Integer

For check = 1 To 12
Range(ActiveCel l.Offset(29, 0), ActiveCell.Offs et(0, 0)).Select
Selection.Copy
ActiveCell.Offs et(1, 0).Select
ActiveSheet.Pas te
Next

End Sub
CODE]

if you're in cell A2, this code will copy A2:A31 and paste them in A3:A32, and then do it again 12 times, so you'll have from cell A2 to A13 the same (what you had in A2) and then youll have from cell A14 to A 43 what you used to have in A2:A31

So your code should work that way.
Error 400 i think is the one VBA reports when your Excel has a "reference trouble" but im not sure about that.

[Samuels90]

thank you for your response.

I would like to copy A2.R30, then go to A31 and copy the range A31.R60 and so on.

Do you have any suggestion?

[kadghar]

thank you for your response.

I would like to copy A2.R30, then go to A31 and copy the range A31.R60 and so on.

Do you have any suggestion?

i dont think i know where do you want to paste them after you copy them, but you can do it with your code and a few changes

[CODE=vb]Sub mycopytry()
Dim check As Integer
For check = 1 To 12

Range(ActiveCel l.Offset(29, 18), ActiveCell.Offs et(0, 0)).Select
Selection.Copy
ActiveCell.Offs et(1, 0).Select 'Here you decide where you want to paste it
ActiveSheet.Pas te
activecell.offs et(29,0),select 'Just make sure that if you change the paste cell, you should modify this offset as well.
Next
End Sub
[/CODE]

Another way would be using Cells, something like

[CODE=vb]sub mycopytry()
dim i as integer
for i = 1 to 12
range(cells((i-1)*29 + 2,1), cells(i*29+1, 18)).copy
cells((i-1)*29 + 3,1).pastespeci al -4163 'you can use paste, but i like paste special with -4163 since it will only paste values (-4163 is the xlvalues constant)
next
end sub[/CODE]

well, hope that helps

[Samuels90]

Kadghar,

thank you for your help.