Dynamic flexible cell address

[dewsbury]

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!

[Gary''s Student]

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
>
>

[dewsbury]

Perfect ! Thanks