I have 2 sheets. Sheet1 is for data entry. Sheet 2 (Say A4:Z4) collects all
the data. What I would like to do is copy Row 4 and insert it into the next
empty row starting at row 15. In other words if three records have already
been entered then the row for insertion would be Row 18. I visualise this
being done with a macro assigned to a "Save" button on Sheet1. Not sure how
to establish the next empty row. Any easy way to do this?
Sandy

Assume column A will have an entry if the row is in use

Sub ABC()
If IsEmpty(Range("A15")) Then
Set rng = Range("A15")
ElseIf IsEmpty(Range("A16")) Then
Set rng = Range("A16")
Else
Set rng = Range("A15").End(xlDown)(2)
End If
MsgBox rng.Address
End Sub


Now rng should be a reference to the first cell in the next empty row
starting with A15

--
Regards,
Tom Ogilvy




"Sandy" wrote:

> I have 2 sheets. Sheet1 is for data entry. Sheet 2 (Say A4:Z4) collects all
> the data. What I would like to do is copy Row 4 and insert it into the next
> empty row starting at row 15. In other words if three records have already
> been entered then the row for insertion would be Row 18. I visualise this
> being done with a macro assigned to a "Save" button on Sheet1. Not sure how
> to establish the next empty row. Any easy way to do this?
> Sandy
>
>
>

Once again many thanks
Sandy

"Tom Ogilvy" <(E-Mail Removed)> wrote in message
news:8213C797-E8EE-4A76-B482-(E-Mail Removed)...
> Assume column A will have an entry if the row is in use
>
> Sub ABC()
> If IsEmpty(Range("A15")) Then
> Set rng = Range("A15")
> ElseIf IsEmpty(Range("A16")) Then
> Set rng = Range("A16")
> Else
> Set rng = Range("A15").End(xlDown)(2)
> End If
> MsgBox rng.Address
> End Sub
>
>
> Now rng should be a reference to the first cell in the next empty row
> starting with A15
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "Sandy" wrote:
>
>> I have 2 sheets. Sheet1 is for data entry. Sheet 2 (Say A4:Z4) collects
>> all
>> the data. What I would like to do is copy Row 4 and insert it into the
>> next
>> empty row starting at row 15. In other words if three records have
>> already
>> been entered then the row for insertion would be Row 18. I visualise this
>> being done with a macro assigned to a "Save" button on Sheet1. Not sure
>> how
>> to establish the next empty row. Any easy way to do this?
>> Sandy
>>
>>
>>