| Home |
| Search |
| Today's Posts |
|
|
|
#1
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
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
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
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
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| PASTE SPECIAL w/ Macro | Excel Discussion (Misc queries) | |||
| paste special / skip empty cells doesn't work!? | Excel Worksheet Functions | |||
| Macro - Auto Paste Special | Excel Discussion (Misc queries) | |||
| Paste Special Macro - can't undo | New Users to Excel | |||
| Paste Special in a macro | Excel Worksheet Functions | |||
Hybrid Mode
