copy to sheet code question
 

heres my following code form the help of those on here...
now my ? is that if i were to go toa sheet a erase a
report. the code still sends the copied data to its last
used line or (row).
i would have to delete the rows onced used from the
previous report and clear it thru edit menu.
can i put ina empty cell line code or somethign to allow
the reports to "move up " and replace "empty cells"? i
just dont want alot of empty rows if i should erase one
copied data.

any help??

thanks!
m







Sub copy_to_sheet()
copy_to = "Month" & Range("month_val").Value
last_used = Sheets(copy_to).Cells.SpecialCells
(xlCellTypeLastCell).Address
copy_to_address = Rows(Sheets(copy_to).Range
(last_used).Row + 1).Columns(1).Address
If copy_to_address = "$A$3" Then copy_to_address = "$A$1"
Sheets("form").Range("form_data").Copy Destination:=Sheets
(copy_to).Range(copy_to_address)


End Sub



Private Sub Worksheet_Change(ByVal Target As Range)

this_month = 1
dd = Range("start_date").Value
Do While dd < Range("ref_date").Value
dd = 1 + dd
If Day(dd) = 26 Then this_month = 1 + this_month
Loop
Range("month_val").Value = this_month
End Sub