Copying to Next Empty Row and Renaming the Data

[USFengBULLS]

Hello All;
I have a log that users will be adjusting data based off statuses of Drawings. Starting at A11, B11 & C11: Column A is DRW NO., Column B is DRW Description and Column C is Location/Rm.
Over in Column L is the status column where the user will select from drop down list. I have this code working for when that status changes to call certain Macros
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("L:L")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
Select Case Target.Value
Case "APPROVED - FV Req.": Call APPROVED(Target.Row)
Case "APPROVED - NO FV Req.": Call APPROVED(Target.Row)
Case "REVISED/AND RESUBMIT": Call REVISED(Target.Row)
End Select
End If
End Sub

I have this macro for the REVISED part but is not copying the contents in column A, B & C and placing it in the next blank row on the sheet. Anyone know why?
Public Sub REVISED(tRow As Double)

Dim erow As Double
erow = Sheets("DRAWING SCHEDULE").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
Range(Cells(tRow, 1), Cells(tRow, 3)).Copy Sheets("DRAWING SCHDULE").Cells(erow, 1)


End Sub

Also, I need this same macro to rename the contents in C that was copied down with just REV 1 or REV 2 based on how many times this location has been revised.
For instance if LEVEL 1/ AREA A/ RM 126 gets changed to the status of Revised/And Resubmit it will get copied down to the next blank cell (along with the contents of column A and B next to it)Then, Get REV 1 at the end like this LEVEL 1/ AREA A/ RM 126 REV 1. If the that one now has to get revised again it copies it down to the next blank row and gets named LEVEL 1/ AREA A/ RM 126 REV 2 because there is one already before it. I do not know how to have VBA look in that C column to find if there are any that exist before and just REV + 1 and rename it. Any help with this would be greatly appreciated, Thanks.

 

[USFengBULLS]

I Figured out the copy part, Just was missing the E in my sheet Name.
But if anyone could please help in this second part on renaming it with REV at the end and incrementing it by 1 each time, LEVEL 1/ AREA A/ RM 126 REV 1, LEVEL 1/ AREA A/ RM 126 REV 2, LEVEL 1/ AREA A/ RM 126 REV 3
Would be a huge help, Thanks.

 

[DanteAmor]

In your code:

[COLOR=#333333]Range(Cells(tRow, 1), Cells(tRow, 3)).Copy Sheets("[/COLOR][COLOR=#0000ff]DRAWING SCHDULE[/COLOR][COLOR=#333333]").Cells(erow, 1)[/COLOR]

Say: "SCHDULE"

should say: "SCHEDULE"

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("L:L")) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        Select Case Target.Value
            Case "APPROVED - FV Req.": Call APPROVED(Target.Row)
            Case "APPROVED - NO FV Req.": Call APPROVED(Target.Row)
            Case "REVISED/AND RESUBMIT": Call REVISED(Target.Row)
            Case Else
                MsgBox "Text no match"
        End Select
    End If
End Sub

Public Sub REVISED(tRow As Double)


    Dim erow As Double
    erow = Sheets("DRAWING SCHEDULE").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
    Range(Cells(tRow, 1), Cells(tRow, 3)).Copy Sheets("DRAWING SCHEDULE").Cells(erow, 1)


End Sub

 

[USFengBULLS]

Yes, I fixed the copy part. It is working fine now. My question is the renaming part to add REV 1 or REV 2 on that same item that was just copied in C column. It need to increment by 1 each time.
So if the item that was just copied in Column C was LEVEL 1/AREA A/ RM 124 When it is Copied down it need to now be renamed to LEVEL 1/AREA A/ RM 124 REV 1 and if that one gets revised it needs to get copied down and renamed to LEVEL 1/AREA A/ RM 124 REV 2 and so on.

 

[USFengBULLS]

Okay I got much closer now. Only Issue is that it keeps putting another REV 1 at the end. For instance Level1/ Area A/ RM 124 REV 1, then when I run it again it goes to Level1/ Area A/ RM 124 REV 1 REV 1
It keeps adding the REV 1 each time instead of Rev 1 then replacing REV 1 to REV 2 the second time.

Here is the revised Code:
Public Sub REVISED(tRow As Double)

Dim erow As Double
Dim Dn As Range
Dim Rng As Range

'Copy Cells down to next Blank Row
erow = Sheets("DRAWING SCHEDULE").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
Range(Cells(tRow, 1), Cells(tRow, 3)).Copy Sheets("DRAWING SCHEDULE").Cells(erow, 1)


'Renames deplicate to REV #
Set Rng = Range(Range("C11"), Range("c" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
If Not .Exists(Dn.Value) Then
.Add Dn.Value, 0
Else
If .Item(Dn.Value) = 0 Then
.Item(Dn.Value) = .Item(Dn.Value) + 1
Dn.Value = Dn.Value & " REV 1"
End If
End If
Next Dn
End With



End Sub