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

Thanks in advance for your help and time!!!

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
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
0
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!
0
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
0
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
0
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.
0
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. ?
0
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
0