Need to add rows based on previous column values

[csimpson24]

Hello all,

Ripping my hair out over this one and cannot find an answer anywhere. Here is an example of my data. I want to automatically add a new row after the last job entry. So in the example below, I would add a new row for job 106144 with Opr 60, a new row for job 108422 with Opr 30, a new row for Job 108466 with Opr 20, and so on. Any help would be GREATLY appreciated. It's been years since I've written in VBA by the way.

JobNum Company Plant Asm Opr Operation <tbody> </tbody> 106144 GCB GCB 0 10 MECHENG 106144 GCB GCB 0 20 MACH 106144 GCB GCB 0 30 FAB 106144 GCB GCB 0 40 ASSEM 106144 GCB GCB 0 50 TEST <colgroup><col><col span="2"><col span="2"><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

108422	GCB	GCB	0	10	FAB
108422	GCB	GCB	0	20	MACH

<colgroup><col><col span="2"><col span="2"><col></colgroup><tbody>
</tbody>

108446	GCB	GCB	0	10	MACH
108449	GCB	GCB	0	10	FAB
108449	GCB	GCB	0	20	MACH

<colgroup><col><col span="2"><col span="2"><col></colgroup><tbody>
</tbody>

 

[Fluff]

Hi & welcome to the board.

You haven't give us much to go on, but try this

Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column <> 6 Then Exit Sub
Application.EnableEvents = False
   With Target
      .Offset(1).EntireRow.Insert
      .Offset(, -5).Resize(2, 5).FillDown
      .Offset(1, -1).Value = .Offset(, -1).Value + 10
   End With
Application.EnableEvents = True
End Sub

It needs to go in the sheet module.

 

[csimpson24]

Thank you! I can provide more information. What do you need? Here's a portion of my spreadsheet. The red is an example of what I would like to have inserted automatically.
Once I place your code into sheet view, how does it run?

A	B	C	D	E	F
JobNum	Company	Plant	Asm	Opr	Operation
106144	GCB	GCB	0	10	MECHENG
106144	GCB	GCB	0	20	MACH
106144	GCB	GCB	0	30	FAB
106144	GCB	GCB	0	40	ASSEM
106144	GCB	GCB	0	50	TEST
106144	GCB	GCB	0	60	PAINT-PC
108037	GCB	GCB	0	10	MACH
108358	GCB	GCB	0	10	MACH
108396	GCB	GCB	0	10	MACH
108422	GCB	GCB	0	10	FAB
108422	GCB	GCB	0	20	MACH
108423	GCB	GCB	0	10	FAB
108423	GCB	GCB	0	20	MACH
108426	GCB	GCB	0	10	MACH
108426	GCB	GCB	0	20	REWORK
108427	GCB	GCB	0	10	FAB
108427	GCB	GCB	0	20	MACH
108431	GCB	GCB	0	10	MACH
108432	GCB	GCB	0	10	FAB
108432	GCB	GCB	0	20	MACH
108436	GCB	GCB	0	10	FAB
108437	GCB	GCB	0	10	FAB
108438	GCB	GCB	0	10	FAB
108439	GCB	GCB	0	10	FAB
108440	GCB	GCB	0	10	FAB
108446	GCB	GCB	0	10	MACH
108449	GCB	GCB	0	10	FAB
108449	GCB	GCB	0	20	MACH
108480	GCB	GCB	0	10	ASSEM
108480	GCB	GCB	0	20	FAB
108480	GCB	GCB	0	30	MACH
108485	GCB	GCB	0	10	MACH

<colgroup><col><col span="2"><col span="2"><col></colgroup><tbody>
</tbody>

 

[Fluff]

When do you want this to run?
When you enter data in a particular column, or whenever you feel like it?
How will the macro know what value to put in col F?

 

[csimpson24]

When do you want this to run?
When you enter data in a particular column, or whenever you feel like it?
How will the macro know what value to put in col F?

I would like to be able to run it when I need to run it. Column F would always be PAINT-PC.

 

[Fluff]

Ok, try

Sub AddRow()

   Dim Rw As Long
   
   For Rw = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
      If Range("A" & Rw) <> Range("A" & Rw + 1).Value Then
         Rows(Rw + 1).Insert
         Range("A" & Rw).Resize(2, 3).FillDown
         Range("E" & Rw + 1).Value = Range("E" & Rw).Value + 10
         Range("F" & Rw + 1).Value = "Paint-PC"
      End If
   Next Rw
         
End Sub

 

[csimpson24]

Works amazingly well! One small request. Column D is blank in the new row. Where do I add that in your code? I am blown away with how much I've lost since doing VBA 10 years ago... lol

 

[csimpson24]

It will always be zero

 

[Fluff]

I can't count!!
it should be

Range("A" & Rw).Resize(2, [COLOR=#ff0000]4[/COLOR]).FillDown

 

[csimpson24]

Nevermind. I got it! Thank you VERY much!!