|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 5th, 2008, 03:17 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How 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 
|
|
#2
May 5th, 2008, 04:07 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set macro to Paste Special Value to next empty column
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 -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... 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
|
|
#3
May 5th, 2008, 04:36 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set macro to Paste Special Value to next empty column
On May 5, 11:07 pm, "Don Guillett" wrote:
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 -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... 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 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
|
|
#5
May 5th, 2008, 05:55 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set macro to Paste Special Value to next empty column
On May 6, 12:00 am, "Don Guillett" wrote:
nc = Cells(sr, Columns.Count).End(xlToLeft).Column + 1 nc = Cells(sr, mc).End(xlToLeft).Column + 1 -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... On May 5, 11:07 pm, "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... 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 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 Where do i put this 2 lines?
|
|
#6
May 5th, 2008, 06:10 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set macro to Paste Special Value to next empty column
First. Many of us prefer TOP posting here.
Second. Put on your thinking cap and look at the macro againand again. -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... On May 6, 12:00 am, "Don Guillett" wrote: nc = Cells(sr, Columns.Count).End(xlToLeft).Column + 1 nc = Cells(sr, mc).End(xlToLeft).Column + 1 -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... On May 5, 11:07 pm, "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... 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 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 Where do i put this 2 lines?
|
|
#7
May 5th, 2008, 06:26 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set macro to Paste Special Value to next empty column
On May 6, 1:10 am, "Don Guillett" wrote:
First. Many of us prefer TOP posting here. Second. Put on your thinking cap and look at the macro againand again. -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... On May 6, 12:00 am, "Don Guillett" wrote: nc = Cells(sr, Columns.Count).End(xlToLeft).Column + 1 nc = Cells(sr, mc).End(xlToLeft).Column + 1 -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... On May 5, 11:07 pm, "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... 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 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 Where do i put this 2 lines? I dont know a single bit of programming that is why i am asking the qn
|
|
#8
May 5th, 2008, 10:39 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set macro to Paste Special Value to next empty column
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik ---
|
|
#9
May 5th, 2008, 11:38 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set macro to Paste Special Value to next empty column
Max, The point was to replace the old nc= with the new, not to have both. -- Don Guillett Microsoft MVP Excel SalesAid Software "Max" wrote in message ... 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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik ---
|
|
#10
May 5th, 2008, 11:57 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set macro to Paste Special Value to next empty column
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Don Guillett" wrote in message ... Max, The point was to replace the old nc= with the new, not to have both.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
