How to set macro to Paste Special Value to next empty column

  • Thread starter Thread starter tottigoh781
  • Start date Start date
T

tottigoh781

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
 
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
Click to expand...

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
 
First. Many of us prefer TOP posting here.
Second. Put on your thinking cap and look at the macro again>and again.
 
First. Many of us prefer TOP posting here.
Second. Put on your thinking cap and look at the macro again>and again.
Click to expand...

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


---
 
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
 
Back
Top