Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range names in macros - DA
One of the things I don't like about macros is that, if you insert rows, it
will mess up macros programmed to go to specific locations. As my massive files evolve, I become paranoid, not to do anything that could move things around (and I'm not organized enough to keep a log of such cells). This handcuffs me. I understand that, if you give a cell(s) a range name, then you can use that instead and then the macro will adjust if the cell gets moved. Can someone write me the equivalent of the following code if I name the first cell "Joe" and the block of cells "Jane"? Application.Goto Reference:="R16C4" ActiveCell.FormulaR1C1 = "0" Selection.Copy Range("D16:D18").Select Range(Selection, Selection.End(xlToRight)).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Thanks! Dean |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range names in macros - DA
Dim rng as Range With Range("Jane") set rng = .Parent.Range(.Cells,.End(xltoRight)) End With Range("Joe").copy rng Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False -- Regards, Tom Ogilvy "Dean" wrote: One of the things I don't like about macros is that, if you insert rows, it will mess up macros programmed to go to specific locations. As my massive files evolve, I become paranoid, not to do anything that could move things around (and I'm not organized enough to keep a log of such cells). This handcuffs me. I understand that, if you give a cell(s) a range name, then you can use that instead and then the macro will adjust if the cell gets moved. Can someone write me the equivalent of the following code if I name the first cell "Joe" and the block of cells "Jane"? Application.Goto Reference:="R16C4" ActiveCell.FormulaR1C1 = "0" Selection.Copy Range("D16:D18").Select Range(Selection, Selection.End(xlToRight)).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Thanks! Dean |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range names in macros - DA
thanks tom
"Tom Ogilvy" wrote in message ... Dim rng as Range With Range("Jane") set rng = .Parent.Range(.Cells,.End(xltoRight)) End With Range("Joe").copy rng Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False -- Regards, Tom Ogilvy "Dean" wrote: One of the things I don't like about macros is that, if you insert rows, it will mess up macros programmed to go to specific locations. As my massive files evolve, I become paranoid, not to do anything that could move things around (and I'm not organized enough to keep a log of such cells). This handcuffs me. I understand that, if you give a cell(s) a range name, then you can use that instead and then the macro will adjust if the cell gets moved. Can someone write me the equivalent of the following code if I name the first cell "Joe" and the block of cells "Jane"? Application.Goto Reference:="R16C4" ActiveCell.FormulaR1C1 = "0" Selection.Copy Range("D16:D18").Select Range(Selection, Selection.End(xlToRight)).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Thanks! Dean |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range names in macros - DA
By the way, I assume there is a way to require a password to run each macro
somehow, but it would be better if they could simply be disabled only if and when I choose to do so, say just before I mail it to someone. I'd rather not have to supply a password every time that I run them. Thanks! Dean "Dean" wrote in message ... thanks tom "Tom Ogilvy" wrote in message ... Dim rng as Range With Range("Jane") set rng = .Parent.Range(.Cells,.End(xltoRight)) End With Range("Joe").copy rng Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False -- Regards, Tom Ogilvy "Dean" wrote: One of the things I don't like about macros is that, if you insert rows, it will mess up macros programmed to go to specific locations. As my massive files evolve, I become paranoid, not to do anything that could move things around (and I'm not organized enough to keep a log of such cells). This handcuffs me. I understand that, if you give a cell(s) a range name, then you can use that instead and then the macro will adjust if the cell gets moved. Can someone write me the equivalent of the following code if I name the first cell "Joe" and the block of cells "Jane"? Application.Goto Reference:="R16C4" ActiveCell.FormulaR1C1 = "0" Selection.Copy Range("D16:D18").Select Range(Selection, Selection.End(xlToRight)).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Thanks! Dean |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range names in macros - DA
sorry, I posted this to the wrong thread. Ignore it here.
"Dean" wrote in message ... By the way, I assume there is a way to require a password to run each macro somehow, but it would be better if they could simply be disabled only if and when I choose to do so, say just before I mail it to someone. I'd rather not have to supply a password every time that I run them. Thanks! Dean "Dean" wrote in message ... thanks tom "Tom Ogilvy" wrote in message ... Dim rng as Range With Range("Jane") set rng = .Parent.Range(.Cells,.End(xltoRight)) End With Range("Joe").copy rng Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False -- Regards, Tom Ogilvy "Dean" wrote: One of the things I don't like about macros is that, if you insert rows, it will mess up macros programmed to go to specific locations. As my massive files evolve, I become paranoid, not to do anything that could move things around (and I'm not organized enough to keep a log of such cells). This handcuffs me. I understand that, if you give a cell(s) a range name, then you can use that instead and then the macro will adjust if the cell gets moved. Can someone write me the equivalent of the following code if I name the first cell "Joe" and the block of cells "Jane"? Application.Goto Reference:="R16C4" ActiveCell.FormulaR1C1 = "0" Selection.Copy Range("D16:D18").Select Range(Selection, Selection.End(xlToRight)).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Thanks! Dean |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting email address names in a range of cells to real names | Excel Worksheet Functions | |||
version differences in excel 2007 - use of macros & range names | Excel Discussion (Misc queries) | |||
COPYING FORMULA CONTAINING NAMES/RELATIVE RANGE NAMES | Excel Discussion (Misc queries) | |||
Defined names in Macros | Excel Programming | |||
Range names in a Excell macros | Excel Programming |