Find last open cell in colomn

[Closed]
Report
-
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
-
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

Thanks in advance for your help and time!!!

4 replies

Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Can you elobrate your problem,

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!
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
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
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
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. ?
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
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