Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Copy Active Cell to Range Name, loop
Hi, I need to create a macro to run the following sequence:
1. Copy the Active Cell (say it's A1, "mark") 2. Move up to the Name Box at top left corner to name the cell range (Is this correct for that location? -- ActiveWorkbook.Names.Add Name:="servera", RefersToR1C1:="=Sheet1!R1C1" 3. Paste what was copied from the active cell and Enter, to name the cell range. 4. Move to another cell, say A3 ("david) and restart the process, copying the active cell (which will hold a different name) Please use example below: A B C D E 1 mark 2 john 3 david 4 larry 5 michael Thanks!!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Copy Active Cell to Range Name, loop
Sub NameMe()
Dim strName As String Dim strAddr As String strName = ActiveCell.Value strAddr = ActiveCell.Address(, , xlR1C1) ActiveWorkbook.Names.Add _ Name:=strName, _ RefersToR1C1:="=Sheet1!" & strAddr End Sub You can use Offset to move from cell to cell, and loop as required. HTH Ed "zigstick" wrote in message om... Hi, I need to create a macro to run the following sequence: 1. Copy the Active Cell (say it's A1, "mark") 2. Move up to the Name Box at top left corner to name the cell range (Is this correct for that location? -- ActiveWorkbook.Names.Add Name:="servera", RefersToR1C1:="=Sheet1!R1C1" 3. Paste what was copied from the active cell and Enter, to name the cell range. 4. Move to another cell, say A3 ("david) and restart the process, copying the active cell (which will hold a different name) Please use example below: A B C D E 1 mark 2 john 3 david 4 larry 5 michael Thanks!!!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Copy Active Cell to Range Name, loop
Thanks so much Ed.
One more question. Say I wanted to 1. copy the data from the active cell 2. select from the active cell to 3 rows down 3. move to the name box and paste the copied data, thus naming the range what was in the initial active.cell Thanks!!!!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Copy Active Cell to Range Name, loop
Assume the activecell is Cell B10 and it contains the string "ABCD". You
want to Name B13 "ABCD" ActiveCell.Offset(3,0).Name = ActiveCell.Value -- Reards, Tom Ogilvy wrote in message oups.com... Thanks so much Ed. One more question. Say I wanted to 1. copy the data from the active cell 2. select from the active cell to 3 rows down 3. move to the name box and paste the copied data, thus naming the range what was in the initial active.cell Thanks!!!!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Copy Active Cell to Range Name, loop
Thanks, but actually trying to capture the entire range of say, Active
cell of b10 to B13 (B10:B13), then move to the name field and type only the data copied from the initial active cell. This would create a named range, with the name being whatever is in the first cell ... in this case, b10. Thanks!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Copy Active Cell to Range Name, loop
If you want to name a cell only, Tom's code is much better. If you want to
name a range of selected cells, I have this to offer: Sub NameMe() Dim strName As String Dim rngMine As Range 'Get text from cell for range name strName = ActiveCell.Value 'Selects from ActiveCell to 3 cells down in same column 'If you do not always want to do this, 'set up an If statement Range(ActiveCell, ActiveCell.Offset(3, 0)).Select 'Sets a rnage to the selection and names it Set rngMine = Selection ActiveWorkbook.Names.Add _ Name:=strName, _ RefersTo:="=Sheet1!" & rngMine.Address End Sub A couple of other items: -- Tom's code is ALWAYS much better than mine! I offerred this only because the way I read your post you wanted a range of cells, and Tom's looked like he only named a single cell. If Tom offers an improvement on this, go with that one. -- You do not "move to the name box" as you would move from cell to cell. You access the Names collection of the Workbook and Add a name. -- You will find a lot of answers faster than waiting for posts on the newsgroup by working through the Help files in the VBA editor. You can type a key word or two describing what you want to do ( such as "name cells") in the Answer Wizard, and it will give you a list of topics. It's often a lot to wade through, but you'll get farther than you will by sitting and waiting. Another highly recommended way is a Google search of the newsgroup. Ron DeBruin has created an excellent Add-In that I use all the time - http://www.rondebruin.nl/Google.htm. Ed (PS - I'm offline now for about 18 hours, so I won't be able to answer anything else for a while.) wrote in message oups.com... Thanks so much Ed. One more question. Say I wanted to 1. copy the data from the active cell 2. select from the active cell to 3 rows down 3. move to the name box and paste the copied data, thus naming the range what was in the initial active.cell Thanks!!!!! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Copy Active Cell to Range Name, loop
ActiveCell.Resize(3,1).name = ActiveCell.Value
-- Regards, Tom Ogilvy wrote in message ups.com... Thanks, but actually trying to capture the entire range of say, Active cell of b10 to B13 (B10:B13), then move to the name field and type only the data copied from the initial active cell. This would create a named range, with the name being whatever is in the first cell ... in this case, b10. Thanks!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy name from active sheet to cell - using macro or function | Excel Worksheet Functions | |||
need to Copy or Move to active cell from specified range | Excel Discussion (Misc queries) | |||
Macro to copy cell data to word document based on an active row? | Excel Programming | |||
declaring active cell inside a for loop and offsetting from it | Excel Programming | |||
How? Macro to copy range to new worksheet, name new worksheet, loop | Excel Programming |