 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008How to set macro to Paste Special Value to next empty column
Range("P32,Q7:Q32").Select Range("Q7").Activate ActiveWindow.SmallScroll Down:=-12 Range("Q7:Q32").Select Selection.Copy Selection.End(xlToLeft).Select Selection.End(xlToRight).Select Selection.End(xlToLeft).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False I try to use the control left then click the right button to go to the next empty column. However, the macro fail to copy to the next available column. Please help. Thanks try Sub makevalues() mc = "q" sr=7 lr = Cells(Rows.Count, mc).End(xlUp).Row nc = Cells(sr, Columns.Count).End(xlToLeft).Column + 1 Range(Cells(sr, mc), Cells(lr, mc)).Copy Cells(sr, nc).PasteSpecial Paste:=xlValues Application.CutCopyMode = False End Sub  SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Range("P32,Q7:Q32").Select > Range("Q7").Activate [quoted text clipped - 13 lines] > > Please help. Thanks > try > [quoted text clipped - 33 lines] > > > Please help. Thanks Thanks for the response. However, the value get pasted to the column R. What happen is that my values in column Q is updated monthly. After each update i need to paste special value to column C then column D in the next month and so on. So i need the macro to recognise the next empty column to the left of column Q. Please help. Thanks nc = Cells(sr, Columns.Count).End(xlToLeft).Column + 1 nc = Cells(sr, mc).End(xlToLeft).Column + 1 SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com >> try >> [quoted text clipped - 39 lines] > the next month and so on. So i need the macro to recognise the next > empty column to the left of column Q. Please help. Thanks > nc = Cells(sr, Columns.Count).End(xlToLeft).Column + 1 > nc = Cells(sr, mc).End(xlToLeft).Column + 1 [quoted text clipped - 50 lines] > > the next month and so on. So i need the macro to recognise the next > > empty column to the left of column Q. Please help. Thanks Where do i put this 2 lines? First. Many of us prefer TOP posting here. Second. Put on your thinking cap and look at the macro again>and again. SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com >> nc = Cells(sr, Columns.Count).End(xlToLeft).Column + 1 >> nc = Cells(sr, mc).End(xlToLeft).Column + 1 [quoted text clipped - 53 lines] > > Where do i put this 2 lines? > First. Many of us prefer TOP posting here. > Second. Put on your thinking cap and look at the macro again>and again. [quoted text clipped - 63 lines] > > > Where do i put this 2 lines? I dont know a single bit of programming that is why i am asking the qn Try Don's revised sub like this: Sub makevalues() mc = "q" sr = 7 lr = Cells(Rows.Count, mc).End(xlUp).Row nc = Cells(sr, Columns.Count).End(xlToLeft).Column + 1 nc = Cells(sr, mc).End(xlToLeft).Column + 1 Range(Cells(sr, mc), Cells(lr, mc)).Copy Cells(sr, nc).PasteSpecial Paste:=xlValues Application.CutCopyMode = False End Sub SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- Max, The point was to replace the old nc= with the new, not to have both. SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Try Don's revised sub like this: > [quoted text clipped - 10 lines] > Application.CutCopyMode = False > End Sub Don, thanks for the clarification. Apologies for my error. It should be: Sub makevalues() mc = "q" sr = 7 lr = Cells(Rows.Count, mc).End(xlUp).Row nc = Cells(sr, mc).End(xlToLeft).Column + 1 Range(Cells(sr, mc), Cells(lr, mc)).Copy Cells(sr, nc).PasteSpecial Paste:=xlValues Application.CutCopyMode = False End Sub SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > > Max, > The point was to replace the old nc= with the new, not to have both. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.