Dynamic flexible cell address
 

I have 1000 names in sheet1. (Cells A1 to A1000)

I want to put names 1 to 10 in Sheet2 - Cells A1 to A10
I want to put names 11 to 20 in Sheet3 - Cells A1 to A10
I want to put names 21 to 30 in Sheet4 - Cells A1 to A10

etc.

until all 1000 names used up.

I want to be able to cut & paste from sheet 2 to 3 , 4 etc.
However, after the C&P I do not want to have to change every address.
I.e. In sheet 3. I do not want to be forced to changed to change cell
A1 to =sheet1!A11

I need a form of "dynamic" cell addressing. Something like
=a(x*10+1)..

Do you understand my query? If so well done!


--
dewsbury
------------------------------------------------------------------------
dewsbury's Profile: http://www.excelforum.com/member.php...o&userid=16084
View this thread: http://www.excelforum.com/showthread...hreadid=520297

Dynamic flexible cell address
 

Let's assume you have 101 worksheets. Sheet1 with the data and sheets 2-101
all blank. Enter and run this macro:


Sub Macro1()
' gsnu
Dim r1, r2 As Range
For i = 2 To 101
Set r2 = Worksheets(i).Range("A1")
Set r1 = Worksheets(1).Range(Cells((i - 2) * 10 + 1, "A"), Cells((i - 2) *
10 + 10, "A"))
r1.Copy r2
Next
End Sub
--
Gary's Student


"dewsbury" wrote:


I have 1000 names in sheet1. (Cells A1 to A1000)

I want to put names 1 to 10 in Sheet2 - Cells A1 to A10
I want to put names 11 to 20 in Sheet3 - Cells A1 to A10
I want to put names 21 to 30 in Sheet4 - Cells A1 to A10

etc.

until all 1000 names used up.

I want to be able to cut & paste from sheet 2 to 3 , 4 etc.
However, after the C&P I do not want to have to change every address.
I.e. In sheet 3. I do not want to be forced to changed to change cell
A1 to =sheet1!A11

I need a form of "dynamic" cell addressing. Something like
=a(x*10+1)..

Do you understand my query? If so well done!


--
dewsbury
------------------------------------------------------------------------
dewsbury's Profile: http://www.excelforum.com/member.php...o&userid=16084
View this thread: http://www.excelforum.com/showthread...hreadid=520297

Dynamic flexible cell address
 

Perfect ! Thanks


--
dewsbury
------------------------------------------------------------------------
dewsbury's Profile: http://www.excelforum.com/member.php...o&userid=16084
View this thread: http://www.excelforum.com/showthread...hreadid=520297