Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Cut and paste macro


(!)

khyskell's Avatar
khyskell khyskell is offline
Junior Member with 7 posts.
THREAD STARTER
 
Join Date: Aug 2008
14-Aug-2008, 07:57 AM #1
Cut and paste macro
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!!
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,616 posts.
 
Join Date: Mar 2005
Location: UK
14-Aug-2008, 08:30 AM #2
After
Selection.End(xlToRight).Select
add
ActiveCell.Offset(0, 1).Select
khyskell's Avatar
khyskell khyskell is offline
Junior Member with 7 posts.
THREAD STARTER
 
Join Date: Aug 2008
14-Aug-2008, 08:53 AM #3
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!
khyskell's Avatar
khyskell khyskell is offline
Junior Member with 7 posts.
THREAD STARTER
 
Join Date: Aug 2008
14-Aug-2008, 09:00 AM #4
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!!
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,616 posts.
 
Join Date: Mar 2005
Location: UK
14-Aug-2008, 09:09 AM #5
Do you only want to paste the values, not just a simple Paste?
khyskell's Avatar
khyskell khyskell is offline
Junior Member with 7 posts.
THREAD STARTER
 
Join Date: Aug 2008
14-Aug-2008, 09:14 AM #6
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!!
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,616 posts.
 
Join Date: Mar 2005
Location: UK
14-Aug-2008, 09:15 AM #7
This works for me
Selection.Copy
Range("F4").Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
khyskell's Avatar
khyskell khyskell is offline
Junior Member with 7 posts.
THREAD STARTER
 
Join Date: Aug 2008
14-Aug-2008, 09:40 AM #8
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!
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,616 posts.
 
Join Date: Mar 2005
Location: UK
14-Aug-2008, 09:46 AM #9
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?
__________________
OBP
I do not give up easily
khyskell's Avatar
khyskell khyskell is offline
Junior Member with 7 posts.
THREAD STARTER
 
Join Date: Aug 2008
14-Aug-2008, 09:56 AM #10
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
File Type: xls TEst.xls (54.0 KB, 84 views)
khyskell's Avatar
khyskell khyskell is offline
Junior Member with 7 posts.
THREAD STARTER
 
Join Date: Aug 2008
14-Aug-2008, 10:03 AM #11
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!!
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,616 posts.
 
Join Date: Mar 2005
Location: UK
14-Aug-2008, 10:30 AM #12
Well done.

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.
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
14-Aug-2008, 04:58 PM #13
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.
Aj_old's Avatar
Aj_old Aj_old is offline
Computer Specs
Senior Member with 869 posts.
 
Join Date: Sep 2007
Location: Moldova
Experience: Intermediate
15-Aug-2008, 02:14 AM #14
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"
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
15-Aug-2008, 05:13 AM #15
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.

BTW, out of curiosity, what's "the BestS"?
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑