Help with macro

[MichaelRobert]

I am having a problem with a Macro.

I have a set of part numbers in cells F7:M7. I want to Copy/Paste
Special>Values down to the next available line at the bottom of the
worksheet, (currently line 20) so I want to paste F7:M7 into the blank cells
F20:M20.

Excel does this fine, and the macro records fine. But when it runs it does
not know which data to use. I think the problem is that the location of the
next available line keeps changing (plus one) each time I try to run the
macro.

Mike

 

[Gary''s Student]

Try this:

Sub michael()
Dim r As Range, r2 As Range
Set r = Range("F7:M7")
n = Cells(Rows.Count, "F").End(xlUp).Row + 1
Set r2 = Range("F" & n)
r.Copy r2
End Sub

 

[Rick Rothstein]

You can shorten your macro a bit...

Sub Mike()
Range("F7:M7").Copy Cells(Rows.Count, "F").End(xlUp).Offset(1)
End Sub

 

[MichaelRobert]

Many thanks, 'Gary's student' and Rick.

Mike

You can shorten your macro a bit...

Sub Mike()
Range("F7:M7").Copy Cells(Rows.Count, "F").End(xlUp).Offset(1)
End Sub

 

[MichaelRobert]

The 'offset' command works fine, but i am having difficulty embedding it into
the "Paste Special > values" macro which I recorder earlier. There is
something I am not getting about specifying the destination range.

Here is the code that I want to modify so that the destination range is the
'offset' range rather than "A20":

Sub PasteSpecial()
Range("A4:E4").Select
Application.CutCopyMode = False
Selection.Copy
Range("A20").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

Thanks again.

Mike