copy row to different sheet based on cell Solved/Closed

Question

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.

Configuration: Windows 7 / Firefox 3.6.3

rizvisa1 · Answer

Could you please upload a sample file with sample data etc on some shared site like https://authentification.site  , 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 · Answer

Here is the link to the sample file
https://authentification.site/files/22862883/Log_-_Copy.xlsm
password is jatejutose

rizvisa1 · Answer

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 · Answer

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 · Answer

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 · Answer

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 · Answer

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

Copy row to different sheet based on cell

7 responses

Similar discussions

Subscribe To Our Newsletter!