VBA to insert rows based on data in one cell & populate

[ALAMZEB]

Hi Guys
Need little help in putting macros below
Column A B C D E F have Vlookup formulas, looking from another sheet
Can we generate macros that whenever ALL Brans 600ml x 24 PET appears in any cell of column A, 9 new rows would be created just below that cell, and populate with data highlighted in blue
Guys just a caution that whole column A is filled with Vlookup formulas.
Thanks in advance guys, really appreciate your help

 

[Khalid NGO]

Hi Alamzeb,

I novice in VB, I tried to shorten but

Sub Insert9_Rows()

Application.ScreenUpdating = False

nr = Cells(Rows.Count, 1).End(xlUp).Row
For r = nr To 10 Step -1

If Cells(r, 1).Text = "ALL Brands 600ml x 24 PET" Then

Rows(r + 1).Insert Shift:=xlDown
Rows(r + 2).Insert Shift:=xlDown
Rows(r + 3).Insert Shift:=xlDown
Rows(r + 4).Insert Shift:=xlDown
Rows(r + 5).Insert Shift:=xlDown
Rows(r + 6).Insert Shift:=xlDown
Rows(r + 7).Insert Shift:=xlDown
Rows(r + 8).Insert Shift:=xlDown
Rows(r + 9).Insert Shift:=xlDown


Rows(r + 1).Interior.ColorIndex = 11
Rows(r + 2).Interior.ColorIndex = 11
Rows(r + 3).Interior.ColorIndex = 11
Rows(r + 4).Interior.ColorIndex = 11
Rows(r + 5).Interior.ColorIndex = 11
Rows(r + 6).Interior.ColorIndex = 11
Rows(r + 7).Interior.ColorIndex = 11
Rows(r + 8).Interior.ColorIndex = 11
Rows(r + 9).Interior.ColorIndex = 11
Application.ScreenUpdating = True

End If

Next
End Sub

Regards,

 

[ALAMZEB]

Hi Khalid

sorry for late reply, but it doesnt work.
would you be able to attach your working

tanks

 

[Khalid NGO]

Hi Alamzeb,
File attached...

But i m not sure it is workable for you or not.
if not, wait for someone to came up with solution instead off creating a copy thread.
Regards,