Cut, Paste and Insert by Range Name
I am on a Mac using Office 2011. I have encountered several different versions of VBA that have very different syntax for these same functions.
I simply want to give a cell range, a name, then instruct the macro to copy, cut and/or insert past those cells based upon those cell range names.
This seems to be working for "Naming" the cell at least there are no compile or run-time error messages.
Sheet1.Names.Add Name:="SDate", RefersTo:="BA6:BA2000", Visible:=True
Sheet1.Names.Add Name:="SOpen", RefersTo:="BB6:BB2000", Visible:=True
Sheet1.Names.Add Name:="SHigh", RefersTo:="BC6:BC2000", Visible:=True
Sheet1.Names.Add Name:="SLow", RefersTo:="BD6:BD2000", Visible:=True
Sheet1.Names.Add Name:="SClose", RefersTo:="BE6:BE2000", Visible:=True
Sheet1.Names.Add Name:="SVolume", RefersTo:="BF6:BF2000", Visible:=True
Sheet1.Names.Add Name:="SAdjClose", RefersTo:="BG6:BG2000", Visible:=True
What is not working is when I attempt the following:
Range("SAdjClose").Select
Selection.Cut
Range("SClose").Select
Selection.Paste
Range("SVolume").Select
Selection.Cut
Range("SOpen").Select
Selection.Insert Shift:=xlToRight
Can someone point out the correct syntax or formats for these actions?
Thanks, Michael
Re: Cut, Paste and Insert by Range Name
first you should avoid selecting an working with selection
like
Code:
Range("SAdjClose").Cut
second are you sure that sheet1 is the active sheet
it is preferable to use the name of the sheet, rather than the code name, which may be different
you need an = in your refersto string for the named range to be defined correctly and probably to make the range addresses absolute, else they maybe relative to cursor position
the default object to contain named ranges is the workbook, though sheets can also contain named ranges
Re: Cut, Paste and Insert by Range Name
Thanks,
I utilized sheet1 because I wanted to be able to "Move or Copy" the sheet so that multiple stocks could be analyzed, one on each sheet.
Each sheet tab will be labeled with the trading symbol of the stock that is the subject of the sheet.
With that info, what should I utilize in place of "sheet1."
Thanks
Re: Cut, Paste and Insert by Range Name
Quote:
With that info, what should I utilize in place of "sheet1."
sheets("sheetname")
