Copy row to different sheet based on cell [Solved/Closed]

gregoryps 8 Posts Tuesday June 8, 2010Registration date June 11, 2010 Last seen - Jun 8, 2010 at 01:57 PM - Latest reply: gregoryps 8 Posts Tuesday June 8, 2010Registration date June 11, 2010 Last seen
- Jun 9, 2010 at 11:05 AM
Hello,

I am need to have a script run each time a cell changes to a date in column O, line 12 and after. I have a spreadsheet that has one tab called Daily log. In Column O, starting at row 12, if a change is made in any row on that sheet row 12 and on for column O and a date is entered I need it to copy the entire row to a different sheet called Daily Closures on the next empty row. I can not figure out how to do it. I am very new to working on this kind of think. Any help would be great.

See more 

8 replies

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 8, 2010 at 02:06 PM
0
Thank you
Could you please upload a sample file with sample data etc on some shared site like http://www.speedyshare.com/ , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
gregoryps 8 Posts Tuesday June 8, 2010Registration date June 11, 2010 Last seen - Jun 8, 2010 at 02:13 PM
0
Thank you
Here is the link to the sample file
http://www.speedyshare.com/files/22862883/Log_-_Copy.xlsm
password is jatejutose
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 8, 2010 at 03:46 PM
0
Thank you
So basically you want that if a value is change in column O when row 12 and more, you want to copy the row.
What if you change the row first time and than realize that date is wrong, In that case again copy is ok? If not then how would you located where is the row that was last copied on Daily Closures?
gregoryps 8 Posts Tuesday June 8, 2010Registration date June 11, 2010 Last seen - Jun 8, 2010 at 03:53 PM
0
Thank you
it really would only need to be if any value was in any cell in Column O row 12 and after. If there is a date in the cell then the row needs to be copied to the sheet.

Thank you
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 8, 2010 at 04:04 PM
0
Thank you
I think I might have not been clear. So there was date, and as soon as you change the value, either by entering a new date or changing from blank, that row would be copied. The issue would be,
Day 1: 012 is blank and I enter 3/1/2010
Day2: 012 is 3/1/2010 and I changed it to 5/1/2010

Now does the row get copied two times ?
and if not then question is how do you locate where is the row that was copied last time
gregoryps 8 Posts Tuesday June 8, 2010Registration date June 11, 2010 Last seen - Jun 8, 2010 at 04:50 PM
0
Thank you
that should not be a problem as they will only be entering a date once. If by error they entered it twice then we could just manually delete one of the lines on the sheet.
Thank you
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 8, 2010 at 05:11 PM
-1
Thank you
Try this


1. Press ALT + F11 to start VBE
2. Press CTRL + R to start Project Explorer
3. Double click on Daily log
4. Paste the code

Private Sub Worksheet_Change(ByVal Target As Range)  
Dim Cell As Range  
Dim lUsedRows As Long  

    Set Cell = Sheets("Daily Closures").Cells.Find("*", Cells(1, "A"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious)  
    If Cell Is Nothing Then  
      
        lUsedRows = 1  
      
    Else  
      
        lUsedRows = Cell.Row + 1  
      
    End If  
      
    Application.EnableEvents = False  
      
    For Each Cell In Target  
      
        If Cell.Row < 12 Then GoTo Next_Cell  
        If Cell.Column <> 15 Then GoTo Next_Cell  
        If (Cell = "") Then GoTo Next_Cell  
        If (Not IsDate(Cell)) Then GoTo Next_Cell  
          
        Sheets("Daily Closures").Range(lUsedRows & ":" & lUsedRows) = Range(Cell.Row & ":" & Cell.Row).Value  
          
        lUsedRows =lUsedRows  + 1
          
Next_Cell:  
    Next Cell  
      
    Application.EnableEvents = True  
      
End Sub
gregoryps 8 Posts Tuesday June 8, 2010Registration date June 11, 2010 Last seen - Jun 9, 2010 at 11:05 AM
works great, thank you very much.