1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Cut and paste macro

Discussion in 'Business Applications' started by khyskell, Aug 14, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. khyskell

    khyskell Thread Starter

    Joined:
    Aug 14, 2008
    Messages:
    7
    I need a macro that will cut the data in a specific range of cells in a column and paste it to the column immediately to the right of the last column containing data. I have been able to accomplish the cut and paste bit but it is pasted over the last column of data rather than being next to it. I can't simply specify the range of cells because this needs to be able to grow, so that it would continue to paste NEXT to, on the right of, the last column of data, regardless of how many columns there may be. Here is the code:

    Sub PasteScenario()
    '
    ' PasteScenario Macro
    ' Pastes values for new scenario
    '
    ' Keyboard Shortcut: Ctrl+Shift+P
    '
    ActiveWindow.SmallScroll Down:=-3
    Range("C45:C59").Select
    Selection.Copy
    Selection.End(xlToRight).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    End Sub

    Please help!! I'm kind of in a hurry!!
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    After
    Selection.End(xlToRight).Select
    add
    ActiveCell.Offset(0, 1).Select
     
  3. khyskell

    khyskell Thread Starter

    Joined:
    Aug 14, 2008
    Messages:
    7
    OBP,

    That looks good but I get an error when I try to run it.

    "Application defined or object defined error"

    This is what I have done with the code by your advice:

    Sub PasteScenario()
    '
    ' PasteScenario Macro
    ' Pastes values for new scenario
    '
    ' Keyboard Shortcut: Ctrl+Shift+P
    '
    ActiveWindow.SmallScroll Down:=-3
    Range("C1:C5").Select
    Selection.Copy
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    End Sub


    I appreciate you taking the time to help me!! Please let me know how I can get this to run!
     
  4. khyskell

    khyskell Thread Starter

    Joined:
    Aug 14, 2008
    Messages:
    7
    OBP,

    Sorry, somehow I am now getting a different error but I have the same code. Error "PasteSpecial method of range class failed"

    Again, I really appreciate your help!!:eek:
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Do you only want to paste the values, not just a simple Paste?
     
  6. khyskell

    khyskell Thread Starter

    Joined:
    Aug 14, 2008
    Messages:
    7
    I suppose it could be done either with or without the paste special but when I comment out the Active.Offset the macro runs but it places the copied data at the end of the world!! Cell "XFD". When I stick the Active.Offset back in I get the application defined error that I told you about. Please help!!
     
  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    This works for me
    Selection.Copy
    Range("F4").Select
    ActiveCell.Offset(0, 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
     
  8. khyskell

    khyskell Thread Starter

    Joined:
    Aug 14, 2008
    Messages:
    7
    This works but only for that fixed condition. What I need is a growing document. This macro is attached to a button on the worksheet and the objective is that when it is run the data in a live column will be copied and pasted to the next open column at the end of the current data. Then once new data is entered in the "Live" column the macro could be used again and the data would be pasted to the right of the data which was just pasted there thusly increasing one column worth each time the macro is used but wholly dependent on the data in the columns to the right of the selection set. I know it sounds like a pain but I really need your help!! Thanks!
     
  9. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Why are you going down the sheet, rather than across, which is the conventional method of entering data?
    Also do you have Access?
    I am not the best Excel programmer on here by a long way, but I don't understand why your macro does not work when you add just that one line of code.
    Can you post a copy of the worksheet please?
     
  10. khyskell

    khyskell Thread Starter

    Joined:
    Aug 14, 2008
    Messages:
    7
    The intention is to go across the sheet (to the right) as normally done. The attached excell file has been gutted but still has the area that we need to focus on. The idea is that data is fed to the column in red, the "live" column, from other workbooks (not attached), which works fine. Then before entering new data the user can click the macro and have the data currently in the live column copied and pasted next to the current last column on the right. The next time the user wants to enter new data from the other workbooks to feed to the live column he/she woul hit the macro again and the new current live column would be pasted to the right of the previously pasted live column. I hope I'm making sense. Please take a look!! Thank you once again!!
     

    Attached Files:

  11. khyskell

    khyskell Thread Starter

    Joined:
    Aug 14, 2008
    Messages:
    7
    OBP,

    I GOT IT!!!!

    Sub PasteScenario()
    '
    ' PasteScenario Macro
    ' Pastes values for new scenario
    '
    ' Keyboard Shortcut: Ctrl+Shift+P
    '
    ActiveWindow.SmallScroll Down:=-3
    Range("C45:C59").Select
    Selection.Copy
    Selection.End(xlToRight).Offset(0, 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    End Sub


    We needed to make the selection.end offset and then have that get selected as the range where to be pasted!! Sweet. Thank you very much for your help!!
     
  12. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Well done. :D

    Can I suggest that when you have some time that you have a look at Posts by Zack Baresse, Bomb #21, AJ_old and some of mine to see some more advanced Macro programming. You aren't actually supposed to use "Select" unless it is essential.

    You can do an "Advanced Search" for those posters.
     
  13. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,270
    Sub test()
    x = Range("C45").End(xlToRight).Column + 1
    Range("C45:C59").Copy
    Cells(45, x).PasteSpecial xlPasteValues
    Cells(40, x) = "Sales Change"
    Cells(41, x) = Date
    Application.CutCopyMode = False
    End Sub


    You don't actually need the first line (x = etc.) separate, doing it that way just makes it clearer.
     
  14. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    I think that the Bomb's code is better to use, but I would change it a little:
    Code:
    Sub test()
        x = Range("C45").End(xlToRight).Column + 1
        Range("C45:C59").Copy
        Cells(45, x).PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        Cells(40, x).value = Cells(40, "C").Value
        Cells(41, x).Value  = Cells(41, "C").Value
    End Sub
    This in case the date in not always the current date and the text isn't "Sales Change"
     
  15. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,270
    The "Sales Change" and Date lines were/are optional, just to show that more than the "core task" could be automated, if that suits. As far as I can see, "copying" C40:C41 to the far right does nothing as that range is blank to start with. :confused:

    BTW, out of curiosity, what's "the BestS"?
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/739980