VBA to insert rows in sheet 2 (parts list) at row 5,& move existing datas down while inserting new rows,when drop down value is selected on E4 sheet1.

[nscaria00]

I am trying to get VBA code to insert rows in sheet 2 (parts list) at row 5,& move existing data's down while inserting new rows, when drop down value is selected on E4 sheet1 (RFQ package).

also, if someone enters value on program life column and total volume on all new inserted rows (in sheet 2 Parts list), YR1-yr 10 can autopopulate based on enetered values (Total volume divide by program life)., that would be excellent.

 

[dreid1011]

Will new entries always be entered into row 4 on sheet 1? Or will other rows be used also?

Where does Customer Part # and Description info come from?

 

[nscaria00]

Will new entries always be entered into row 4 on sheet 1? Or will other rows be used also?

Where does Customer Part # and Description info come from?

Yes, always new entry goes to row 4 on sheet 1.
Customer part # and Description is manually typed in, since that's comes through Customer portal. I don't know if I can convert a webpage to excel and pull data to populate in Customer part # and description.. May be that's too much..

 

[dreid1011]

Okay, then perhaps this should get you what you want. Give it a try on a copy of your workbook (copy/paste in the sheet1 code module):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRows As Long, i As Long, j As Long
Dim ws As Worksheet
Set ws = Sheets("parts list")
On Error Resume Next
Application.EnableEvents = False
If Not Intersect(Target, Range("E4")) Is Nothing Then
    iRows = 5 + Range("E4").Value - 1
    For i = 1 To Range("E4").Value
        j = i + 4
        ws.Range("A" & j & ":O" & j).Insert Shift:=xlDown
        ws.Range("A" & j).Value = Range("A4").Value
        ws.Range("F" & j & ":O" & j).Formula2 = "=IF(COLUMNS($F$5:F5)>$E" & j & ","""",$D" & j & "/$E" & j & ")"
    Next i
End If
Application.EnableEvents = True
End Sub

It inserts new rows into sheet 2 determined by the amount selected in E4 on sheet 1. It also enters a formula into the F:O columns to handle your second question.

 

[nscaria00]

Okay, then perhaps this should get you what you want. Give it a try on a copy of your workbook (copy/paste in the sheet1 code module):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRows As Long, i As Long, j As Long
Dim ws As Worksheet
Set ws = Sheets("parts list")
On Error Resume Next
Application.EnableEvents = False
If Not Intersect(Target, Range("E4")) Is Nothing Then
    iRows = 5 + Range("E4").Value - 1
    For i = 1 To Range("E4").Value
        j = i + 4
        ws.Range("A" & j & ":O" & j).Insert Shift:=xlDown
        ws.Range("A" & j).Value = Range("A4").Value
        ws.Range("F" & j & ":O" & j).Formula2 = "=IF(COLUMNS($F$5:F5)>$E" & j & ","""",$D" & j & "/$E" & j & ")"
    Next i
End If
Application.EnableEvents = True
End Sub

It inserts new rows into sheet 2 determined by the amount selected in E4 on sheet 1. It also enters a formula into the F:O columns to handle your second question.

Excellent, that works perfectly!

 

[dreid1011]

Excellent, that works perfectly!

You're welcome, and glad to help.

 

[nscaria00]

One another help please. I have a master excel document which has couple of tabs (shown in tabs to extract image). I am populating data's in three tabs (component, tools and logistics) from other tabs by using "=" function. But I need to extract these three documents before I send to customer.
I am looking for a VBA code to extract these three tabs from master and create another document (with these three tabs) and save with RFQ number from "Pre-review checklist" B1 cell.
In the extracted files, "=" function should not be there and the datas needs to show as values (i mean not linked any more to master file). Is it doable?
I can share the excel document if needs to.
Thanks,

 

[dreid1011]

One another help please. I have a master excel document which has couple of tabs (shown in tabs to extract image). I am populating data's in three tabs (component, tools and logistics) from other tabs by using "=" function. But I need to extract these three documents before I send to customer.
I am looking for a VBA code to extract these three tabs from master and create another document (with these three tabs) and save with RFQ number from "Pre-review checklist" B1 cell.
In the extracted files, "=" function should not be there and the datas needs to show as values (i mean not linked any more to master file). Is it doable?
I can share the excel document if needs to.
Thanks,

As this is a new question, you should post it in its own thread.

 

[nscaria00]

Sorry, I will do that. Thank you!