rows....

[Ankur]

i have a worksheet of vendor account details. in one column there are vendor
numbers & some vendors have 5 line items some have10,15,50,etc i.e. line
items are different. Now the problem is every month i have to prepare vendors
template and i have to insert 5 rows after every change in vendor number in
vendor column.

right now i am going manually at each vendor change and inserting rows as
detail above, there are about 700 vendors. this is not only time consuming
but frustating job.

so there any way to develop macro for this or any other way so that by
giving one command the above problem can be sorted out

thanks a lot in advnace.....

[Paul B]

Ankur, you could use subtotals, Data, subtotals, and check insert page break
between groups, or a macro like this with vendors numbers in column A

Sub Insert_Page_Breaks()
'Will insert a page break at change of data in column A
Set rng = Range(Cells(2, 1), _
Cells(Rows.Count, 1).End(xlUp))
For Each cell In rng
If Trim(cell.Value) <> _
Trim(cell.Offset(-1, 0).Value) Then
ActiveSheet.HPageBreaks.Add cell
End If
Next
End Sub


And to clear all the page breaks

Sub Remove_All_Page_Breaks()
ActiveSheet.ResetAllPageBreaks
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Ankur" <[email protected]> wrote in message
news:[email protected]...
> i have a worksheet of vendor account details. in one column there are
vendor
> numbers & some vendors have 5 line items some have10,15,50,etc i.e. line
> items are different. Now the problem is every month i have to prepare
vendors
> template and i have to insert 5 rows after every change in vendor number
in
> vendor column.
>
> right now i am going manually at each vendor change and inserting rows as
> detail above, there are about 700 vendors. this is not only time consuming
> but frustating job.
>
> so there any way to develop macro for this or any other way so that by
> giving one command the above problem can be sorted out
>
> thanks a lot in advnace.....

[Ken Wright]

Another option:-

Sub InsRows()

Application.ScreenUpdating = False
Dim numRows As Integer
Dim R As Long
Dim rng As Range
Dim LastRw As Long

numRows = 5

LastRw = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range(Cells(1, "A"), Cells(LastRw, "A"))

For R = rng.Rows.Count To 1 Step -1
With Cells(R, "A")
If .Value <> .Offset(1, 0).Value Then
rng.Rows(R + 1).Resize(numRows).EntireRow.Insert
End If
End With
Next R
Application.ScreenUpdating = True

End Sub


--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------

"Ankur" <[email protected]> wrote in message
news:[email protected]...
>i have a worksheet of vendor account details. in one column there are
>vendor
> numbers & some vendors have 5 line items some have10,15,50,etc i.e. line
> items are different. Now the problem is every month i have to prepare
> vendors
> template and i have to insert 5 rows after every change in vendor number
> in
> vendor column.
>
> right now i am going manually at each vendor change and inserting rows as
> detail above, there are about 700 vendors. this is not only time consuming
> but frustating job.
>
> so there any way to develop macro for this or any other way so that by
> giving one command the above problem can be sorted out
>
> thanks a lot in advnace.....

[Ankur]

ken,
thanks.
one more thing in the rows inserted i have to add some information, in first
vendor i have type the information & formulas and then i copy this after
every change in vendor, the information and formula is :

Vendor =+C23
Company Code 7039

Name =INDEX(data,MATCH(E17,vendor,0),2)
City =INDEX(data,MATCH(E17,vendor,0),4)

what i am doing is after every change in vendor i am copying the above
detail so it gives first vendor information and then vendor line items.

Now my ques is can we add this also in the macro you have given?
This will solve my all problem.

Thanks

"Ken Wright" wrote:

> Another option:-
>
> Sub InsRows()
>
> Application.ScreenUpdating = False
> Dim numRows As Integer
> Dim R As Long
> Dim rng As Range
> Dim LastRw As Long
>
> numRows = 5
>
> LastRw = Cells(Rows.Count, "A").End(xlUp).Row
> Set rng = Range(Cells(1, "A"), Cells(LastRw, "A"))
>
> For R = rng.Rows.Count To 1 Step -1
> With Cells(R, "A")
> If .Value <> .Offset(1, 0).Value Then
> rng.Rows(R + 1).Resize(numRows).EntireRow.Insert
> End If
> End With
> Next R
> Application.ScreenUpdating = True
>
> End Sub
>
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ------------------------------Â*------------------------------Â*----------------
> It's easier to beg forgiveness than ask permission :-)
> ------------------------------Â*------------------------------Â*----------------
>
> "Ankur" <[email protected]> wrote in message
> news:[email protected]...
> >i have a worksheet of vendor account details. in one column there are
> >vendor
> > numbers & some vendors have 5 line items some have10,15,50,etc i.e. line
> > items are different. Now the problem is every month i have to prepare
> > vendors
> > template and i have to insert 5 rows after every change in vendor number
> > in
> > vendor column.
> >
> > right now i am going manually at each vendor change and inserting rows as
> > detail above, there are about 700 vendors. this is not only time consuming
> > but frustating job.
> >
> > so there any way to develop macro for this or any other way so that by
> > giving one command the above problem can be sorted out
> >
> > thanks a lot in advnace.....
>
>
>