Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Address | Excel Discussion (Misc queries) | |||
cell address rather than range name | Excel Discussion (Misc queries) | |||
Can the column index in a cell address be made variable? | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
How do I find the contents of a cell using the "ADDRESS" function. | Excel Worksheet Functions |