-
Nov 12th, 2013, 08:56 PM
#1
Thread Starter
Junior Member
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
Last edited by MSlattery; Nov 12th, 2013 at 08:59 PM.
-
Nov 13th, 2013, 03:51 AM
#2
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Nov 13th, 2013, 03:02 PM
#3
Thread Starter
Junior Member
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
-
Nov 13th, 2013, 03:32 PM
#4
Re: Cut, Paste and Insert by Range Name
With that info, what should I utilize in place of "sheet1."
sheets("sheetname")
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Nov 25th, 2013, 03:45 AM
#5
Banned
moncler jacke imitat X11428
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|