Copy Range with variable

[shammi_Raj]

Please Login or Register  to view this content.

Sub

I am trying to copy A1:G7 and paste it as transpose to say J1 ... I have 7 rows filled.. now I have to copy from A3-G9 and paste as J8 .. and like wise ..
I am stuck at how to change the range with help of variables.
my idea is
for k = 1 to 100 step 2
A[k]:G[k+1] ..
how to write this piece with VBA ..

Any help will be highly appreciated ..

Thanks in advance

Shammi

[shammi_Raj]

I found the solution to the problem . Incase anyone might need in future or I need to refer when my poor memory fails to recollect ..

I used

Please Login or Register  to view this content.

The mistake I was making was - using ":" instead of "," in Range ..

[DonkeyOte]

Thank you for both

a) adding tags

and

b) posting your solution

On an aside you could use something like

Please Login or Register  to view this content.

disabling screen repaint and avoiding Select whilst running your routine will improve overall performance

[shammi_Raj]

Thanks .. This solution is awesome .. like it

[shammi_Raj]

Need further help on this .. anyone please

I am able to select range with a variable as in

k =2
Range("A" & k, "G" & k + 1).Select

but when I have to select from other sheet say ..

Range("'Sheet5'!$A$2:$G$3")

How do I go about it ? - My doubt is the syntax for selecting sheet and range in one line .

something like this should help

Range("'Sheet5'" & "A" & k, "G" & k + 1)