# Find last open cell in colomn

Closed
Stormdronk - Apr 5, 2011 at 02:29 AM
RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 - Apr 7, 2011 at 01:02 AM
Hello,

Hope someone can help!

I need a formula for the following.

If a1 = 4 then copy b1, c1, d1, e1,.. to first open cells in sheet 2 coulmn b, c, d, e

## 4 replies

RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
Apr 5, 2011 at 06:00 AM

what if
a1 = 3, then to copy b1, c1 & d1 to sheet 2 or something else.
and what should you need and a1 in sheet2.
is sheet 2 already having some data or it is blank.
and how many rows in sheet1 (it just a single row or multiple)

kindly some part of your data and re-explain what you required to understand the problem,

Regards,
Rahul
Hi, Rahul.

Thanks for trying to help!

I have a sheet as shown below. We do sales every day and would like the values/ estimates and sales that have a status of 4 to be copyd to a total sheet (sheet 2). The content must be copyd as soon as the status becomes 4 and automaticly be moved to the next open cell,s in sheet2.(ranged cells)

Hope this is clear

Sheet 1

Estimate Sale Status
R 10,000.00 R 10,000.00 4
R 5,000.00 R 5,000.00 2
R 3,000.00 1
R 5,000.00 R 5,000.00 4

Sheet 2
Estimate Sale Status
R 10,000.00 R 10,000.00 4
R 5,000.00 R 5,000.00 4

Are there a formula that can do this, dont know macros jet?

hanks again for the help!
RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
Apr 6, 2011 at 01:17 AM
Hi Stormdronk,

little bit more explaination is the looking status value 4 or else, in the same column say C. or something else
Hi,

Yes the status 4 is in the same column, it can be 1 2 3 or 4 and when it is 4 it must copy to sheet 2.

Thanks
RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
Apr 6, 2011 at 05:01 AM
so then you can filter and copy the data in sheet2

just filter and select 4 in the column in which the status mention (Say C)

now copy the filtered data in sheet 2.
Hi,

Yes I can just filter and copy the data in sheet 2, but would like it to move automaticly if the status become 4, if that is posible. ?
RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
Apr 7, 2011 at 01:02 AM
use this macro

```Private Sub Worksheet_copy(ByVal Target As Range)
Dim wSheet As Worksheet
Set wSheet = ActiveSheet
If Target.Value = 4 Then
Target.EntireRow.Copy
Sheets("sheet2").Select
Sheets("sheet2").Range("a" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
wSheet.Select

End If

End Sub
```