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

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
 
D

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
 
T

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
 
D

Don Guillett

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

Don Guillett

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

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
 
M

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
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top