How to reference variable range in a loop

S

Samirkc

Dear all,
In my loop the number of rows for range changes. I have to copy paste from
one Sheet to other. I tried following ways:

....
d =
number_rows = ...
name_range = "a2:i" & number_rows + 1
Sheets(1).range(name_range).Value = Sheets(2).range(name_range).offset
(d,0).Value
.....

This does not work...

the second way I tried

....
d = ...
number_rows = ...
Sheets(1).range(Cells(2,1), Cells(number_rows+1,9)).Value =
Sheets(2).range(Cells(2,1), Cells(number_rows+1+d,9)).Value
.....

This does not work too...
Please help
How to prepare a reference using Concatenate or any other function?
How can one use Cells in Range(...) in multiple Sheets?
Thanks for your time
Samir
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Loop returns only one value, does not loop & an assignment to columnhow-to problem. 26
Modify working code to expand the output. 5
Visio: copy text of a master shape to User-define Cells (Variable) 0
Copy Array to Array, how to and should I? 13
Some trouble assigning a string to a variable 22
Array with many cells from a row but not in order or together 11
Clean this select case code up a bit. 2
Loop is slow trying array code 17

Top