Instead of using Range("B13").Select
Find the last row in column D and than paste there

define the last row + 1

Dim lrow as Long

lrow == Cells(Rows.COUNT, "B").End(xlUp).Offset(1, 0).Row

Also - you don't need to select. So your macro would look like:

''''''''''''''''''''''''''
Dim lrow as Long

lrow == Cells(Rows.COUNT, "B").End(xlUp).Offset(1, 0).Row

Range("B9:I9").Copy
Cells(lrow,2.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Range("B9:F9").ClearContents
Range("A9").Select
''''''''''''''''''''''''''''''''''

hth
--

steveB

(Remove 'NOSPAM' from email address if contacting me direct)


"tomkarakowski " wrote in
message ...
Hello-
I am new to macros and would like some help. I have a spreadsheet that
does some calcuations based on numbers you input (cells B9 to E9
require you to input numbers, F9,G9,H9 and I9 report numbers from
sheet two cells that are generated by the B9 to E9 pn sheet 1).I have a
command button that copies the B9:I9 Line down to B13, then clears B9
to E9 for new data entry. I'd like to be able to hit my button again
and have the macro go to paste at B13, see that data is there and
proceed to B14 and paste. Any help with this is appreciated - below is
what I have assigned to my command button.

Also I only have 97 so keep that in mind
Thanks -Tom


' ADDLINE Macro
' Macro recorded 5/27/2004 by Tom K

Range("B9:I9").Select
Selection.Copy
Range("B13").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("B9:F9").Select
Selection.ClearContents
Range("A9").Select
End Sub


---
Message posted from http://www.ExcelForum.com/