Results 1 to 5 of 5

Thread: Cut, Paste and Insert by Range Name

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Nov 2013
    Posts
    22

    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.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Nov 2013
    Posts
    22

    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

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  5. #5

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
  •  



Click Here to Expand Forum to Full Width