Macro to copy and insert rows in multiple worksheets

[Banesmagic]

I need a macro that will select all of my sheets, find the line 2 rows above a cell called Totals in column A of each sheet, copy that line and then insert the copied cell 5 times.

I have a data entry sheet for quotes with 50 or so lines with formulas to calculate the quote based on costs, rebates etc... I then have 4 different presentation sheets for different customer types which reference the data entry sheet. Each sheet in the workbook has the same number of lines. I want to be able to add 5 lines at a time to each sheet while copying the formulas (both the formulas of the data entry sheet and the references in the other sheets).

Thanks in advance to anyone who can help.

 

[ravishankar]

Hi
Paste the following codes in the macro window ( Alt F11)

Sub jjj()
x = Cells(Rows.Count, 1).End(xlUp).Row
For a = 1 To Sheets.Count
b = "A1:A" & x
c = Application.WorksheetFunction.Match("Total", Worksheets(a).Range(b), 0)
Worksheets(a).Rows(c - 2).Copy
For d = 1 To 5
Worksheets(a).Rows(x + d).PasteSpecial
Next d
Next a
End Sub

Run the macro.
Ravi