I am having a problem with a Macro.

I have a set of part numbers in cells F7:M7. I want to Copy/Paste
SpecialValues 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

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
--
Gary''s Student - gsnu200814

You can shorten your macro a bit...

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

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
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
--
Gary''s Student - gsnu200814

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

Mike

"Rick Rothstein" wrote:

You can shorten your macro a bit...

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

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
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
--
Gary''s Student - gsnu200814

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

"MichaelRobert" wrote:

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

Mike

"Rick Rothstein" wrote:

You can shorten your macro a bit...

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

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
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
--
Gary''s Student - gsnu200814