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

[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

 

[Don Guillett]

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

 

[tottigoh781]

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

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

 

[Don Guillett]

nc = Cells(sr, Columns.Count).End(xlToLeft).Column + 1
nc = Cells(sr, mc).End(xlToLeft).Column + 1

 

[tottigoh781]

nc = Cells(sr, Columns.Count).End(xlToLeft).Column + 1
nc = Cells(sr, mc).End(xlToLeft).Column + 1

Where do i put this 2 lines?

 

[Don Guillett]

First. Many of us prefer TOP posting here.
Second. Put on your thinking cap and look at the macro again>and again.

 

[tottigoh781]

First. Many of us prefer TOP posting here.
Second. Put on your thinking cap and look at the macro again>and again.

I dont know a single bit of programming that is why i am asking the qn

 

[Max]

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 Guillett]

Max,
The point was to replace the old nc= with the new, not to have both.

 

[Max]

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