SOS Help! Code/Conditional Formatting Tables

Question

I have a large table (Row A-S). It has a ton of different formulas and conditional formatting applied to it.

I have a drop down status with a bunch of words in Column "N". Ultimately what I want to do is if the words (Void, Revise & Resubmit, Rejected, or Closed) is selected in Column "N" then I want it to automatically duplicate that row and create a new row while keeping only the text that is is the first (6) rows. But I also wanting it to carry the formulas with it when it duplicates/create the new row.

Basically when (Void, Rejected, etc.) is typed it column "N" in creates a new line item "row" with some of the text that was in the previous row that is now "Voided". But keeps formulas and conditional formatting.

SOS how can I do this or is it even possible. I have been chatting with Excel Autopilot but its not getting me very far.

It gave me a code to but in VBS Module and then it gave me a code for the "Sheet" itself but it still isn't automatically creating the new row unless I run the Macro.

kidd_ip · Answer

How about this:
&nbsp;
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim rng As Range
    Dim newRow As Range
    Dim i As Integer
    
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name
    
    ' Check if the change occurred in Column N
    If Not Intersect(Target, ws.Range("N:N")) Is Nothing Then
        If Target.Value = "Void" Or Target.Value = "Revise &amp; Resubmit" Or Target.Value = "Rejected" Or Target.Value = "Closed" Then
            Application.EnableEvents = False
            
            ' Define the row to be copied
            Set rng = ws.Rows(Target.Row)
            
            ' Insert a new row below
            rng.Copy
            ws.Rows(Target.Row + 1).Insert Shift:=xlDown
            Set newRow = ws.Rows(Target.Row + 1)
            
            ' Convert first six columns to values
            For i = 1 To 6
                newRow.Cells(1, i).Value = newRow.Cells(1, i).Value
            Next i
            
            Application.EnableEvents = True
        End If
    End If
End Sub
&nbsp;

catjohnston · Answer

OMG thank you!&nbsp;If the new row "Revision" is also marked Revise &amp; Resubmit, Void, or Rejected is there a way to have it automatically change to Revision #2 and then so on and so forth.&nbsp;&nbsp;So basically i have my original, then if the original row has Revise &amp; Resubmit, Void, or Rejected then this code tells it to create a new line, copy the information and formulas, and populates a #1 in column "E".But if that new row/revision also gets marked Rejected and i need it to create another row can i tell it to recognize that column "E" already has a (#1) and have it populate a (#2) instead, so on and so forth.&nbsp;&nbsp;Im not sure if that made 100% sense but any help would be greatly appreciated. Code is very new for me and i need to get this master template finished.&nbsp;&nbsp;Also if you know of a code to be able to make all cells adjust, and keep to right font and size if something is copied and pasted into this master template that would be fantastic.&nbsp;Also to have the formulas &amp; conditional formatting i have in place be transferred down if someone pastes information that extends past the rows in the table that i have.&nbsp;&nbsp;I Protect &amp; Lock the sheet and I am essentially trying to make it to where there is no human error since it will be used my multiple different people and they wont have to pull the formulas down.&nbsp;

catjohnston · Answer

OMG thank you!&nbsp;I am also trying to have column "E" be populated with a number (to show which revision it is on)Is there a way when it creates the new row to be populated with a "1" and then if said row gets responded to as Revised &amp; Resubmit and already has a number "1" then the next new row will have a "2" so on and so forth.&nbsp;&nbsp;&nbsp;So basically i have my original, then if the original row has Revise &amp; Resubmit, Void, or Rejected then this code tells it to create a new line, copy the information and formulas, and populates a "1" in column "E".But if that new row/revision also gets marked Rejected and I want it to create another row can I tell it to recognize that column "E" already has a (1) and have it populate a (2) instead, so on and so forth.&nbsp;&nbsp;Im not sure if that made 100% sense but any help would be greatly appreciated. Code is very new for me and I need to get this master template finished.&nbsp;&nbsp;Also if you know of a code to be able to make all cells adjust, and keep to right font and size if something is copied and pasted into this master template that would be fantastic.&nbsp;Also to have the formulas &amp; conditional formatting i have in place be transferred down if someone pastes information that extends past the rows in the table that i have.&nbsp;&nbsp;I Protect &amp; Lock the sheet and I am essentially trying to make it to where there is no human error since it will be used my multiple different people and they wont have to pull the formulas down.&nbsp;

Forum Discussion

SOS Help! Code/Conditional Formatting Tables

3 Replies

Resources