create blank rows
 

Column 'B' on my worksheet is labeled 'Work Order'. Each row, therefore,
will contain data related to that 'Work Order' #. I would like to create a
macro which will allow the user to hit a button which will add 25 rows to the
worksheet. The rows will be blank other than I would like it to assign the
next consecutive work order # (i.e. if we say we have 100 work orders
assigned, I would like to add 25 rows with work order #'s 101-125). It would
be nice if this code would also somehow copy the formatting of the row. Any
ideas out there??

create blank rows
 

Hi,
The following code looks at last used cell in column A then insert 25 rows
from that point. The Insert should carry thew format of the last row over.

Sub test()
Dim InsertionPoint As Range, rg As Range
Dim colToCheck As String
Dim expandBy As Long
Dim wsh As Worksheet

'---- CHANGE HERE -------
Set wsh = ActiveSheet
colToCheck = "A"
expandBy = 25
'------------------------

'Find last currently used row in column colToCheck
Set InsertionPoint = wsh.Range(colToCheck & 65536).End(xlUp).Offset(1,
0).EntireRow

'Insert rows
Set rg = InsertionPoint.Resize(expandBy)
rg.Insert
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Coal Miner" wrote:

Column 'B' on my worksheet is labeled 'Work Order'. Each row, therefore,
will contain data related to that 'Work Order' #. I would like to create a
macro which will allow the user to hit a button which will add 25 rows to the
worksheet. The rows will be blank other than I would like it to assign the
next consecutive work order # (i.e. if we say we have 100 work orders
assigned, I would like to add 25 rows with work order #'s 101-125). It would
be nice if this code would also somehow copy the formatting of the row. Any
ideas out there??

create blank rows
 

Sebastienm,

That portion works great. Thanks! :)

Is there a way to simply accomplish the other request of assigning the next
sequential # in the 'Work Order' column. See my original message text.

"sebastienm" wrote:

Hi,
The following code looks at last used cell in column A then insert 25 rows
from that point. The Insert should carry thew format of the last row over.

Sub test()
Dim InsertionPoint As Range, rg As Range
Dim colToCheck As String
Dim expandBy As Long
Dim wsh As Worksheet

'---- CHANGE HERE -------
Set wsh = ActiveSheet
colToCheck = "A"
expandBy = 25
'------------------------

'Find last currently used row in column colToCheck
Set InsertionPoint = wsh.Range(colToCheck & 65536).End(xlUp).Offset(1,
0).EntireRow

'Insert rows
Set rg = InsertionPoint.Resize(expandBy)
rg.Insert
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Coal Miner" wrote:

Column 'B' on my worksheet is labeled 'Work Order'. Each row, therefore,
will contain data related to that 'Work Order' #. I would like to create a
macro which will allow the user to hit a button which will add 25 rows to the
worksheet. The rows will be blank other than I would like it to assign the
next consecutive work order # (i.e. if we say we have 100 work orders
assigned, I would like to add 25 rows with work order #'s 101-125). It would
be nice if this code would also somehow copy the formatting of the row. Any
ideas out there??

create blank rows
 

sorry, I had forgotten about that part.
At the end of the macro, add the follwing code:
-- assumes that cell in col A contains a number (ordewred properly)
'----------------------------
'Insert numbers
Set rg = Range(InsertionPoint.offset(-expandBy),InsertionPoint.offset(-1))
set rg=application.intersect(rg,rg.parent.range(colToC heck & ":" &
colToCheck))
rg.formula= "=" & rg.cells(1).offset(-1,0).Address(false,false) & "+1"
rg.copy
rg.pastespecial xlPasteValues
application.cutcopymode=false
'--------------------------------------

Would that work? You may have to adjust a bit this latest code as i haven't
checked it.
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Coal Miner" wrote:

Sebastienm,

That portion works great. Thanks! :)

Is there a way to simply accomplish the other request of assigning the next
sequential # in the 'Work Order' column. See my original message text.

"sebastienm" wrote:

Hi,
The following code looks at last used cell in column A then insert 25 rows
from that point. The Insert should carry thew format of the last row over.

Sub test()
Dim InsertionPoint As Range, rg As Range
Dim colToCheck As String
Dim expandBy As Long
Dim wsh As Worksheet

'---- CHANGE HERE -------
Set wsh = ActiveSheet
colToCheck = "A"
expandBy = 25
'------------------------

'Find last currently used row in column colToCheck
Set InsertionPoint = wsh.Range(colToCheck & 65536).End(xlUp).Offset(1,
0).EntireRow

'Insert rows
Set rg = InsertionPoint.Resize(expandBy)
rg.Insert
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Coal Miner" wrote:

Column 'B' on my worksheet is labeled 'Work Order'. Each row, therefore,
will contain data related to that 'Work Order' #. I would like to create a
macro which will allow the user to hit a button which will add 25 rows to the
worksheet. The rows will be blank other than I would like it to assign the
next consecutive work order # (i.e. if we say we have 100 work orders
assigned, I would like to add 25 rows with work order #'s 101-125). It would
be nice if this code would also somehow copy the formatting of the row. Any
ideas out there??

create blank rows
 

Thanks. Works like a charm!

"sebastienm" wrote:

sorry, I had forgotten about that part.
At the end of the macro, add the follwing code:
-- assumes that cell in col A contains a number (ordewred properly)
'----------------------------
'Insert numbers
Set rg = Range(InsertionPoint.offset(-expandBy),InsertionPoint.offset(-1))
set rg=application.intersect(rg,rg.parent.range(colToC heck & ":" &
colToCheck))
rg.formula= "=" & rg.cells(1).offset(-1,0).Address(false,false) & "+1"
rg.copy
rg.pastespecial xlPasteValues
application.cutcopymode=false
'--------------------------------------

Would that work? You may have to adjust a bit this latest code as i haven't
checked it.
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Coal Miner" wrote:

Sebastienm,

That portion works great. Thanks! :)

Is there a way to simply accomplish the other request of assigning the next
sequential # in the 'Work Order' column. See my original message text.

"sebastienm" wrote:

Hi,
The following code looks at last used cell in column A then insert 25 rows
from that point. The Insert should carry thew format of the last row over.

Sub test()
Dim InsertionPoint As Range, rg As Range
Dim colToCheck As String
Dim expandBy As Long
Dim wsh As Worksheet

'---- CHANGE HERE -------
Set wsh = ActiveSheet
colToCheck = "A"
expandBy = 25
'------------------------

'Find last currently used row in column colToCheck
Set InsertionPoint = wsh.Range(colToCheck & 65536).End(xlUp).Offset(1,
0).EntireRow

'Insert rows
Set rg = InsertionPoint.Resize(expandBy)
rg.Insert
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Coal Miner" wrote:

Column 'B' on my worksheet is labeled 'Work Order'. Each row, therefore,
will contain data related to that 'Work Order' #. I would like to create a
macro which will allow the user to hit a button which will add 25 rows to the
worksheet. The rows will be blank other than I would like it to assign the
next consecutive work order # (i.e. if we say we have 100 work orders
assigned, I would like to add 25 rows with work order #'s 101-125). It would
be nice if this code would also somehow copy the formatting of the row. Any
ideas out there??

create blank rows
 

Sebastienm,

One last thing and then I'll stop bugging you. Column C and Column D
contain formulae which I would like the macro to copy down. I was messing
with the xlfilldefault but with no luck:(

Any ideas?

"Coal Miner" wrote:

Thanks. Works like a charm!

"sebastienm" wrote:

sorry, I had forgotten about that part.
At the end of the macro, add the follwing code:
-- assumes that cell in col A contains a number (ordewred properly)
'----------------------------
'Insert numbers
Set rg = Range(InsertionPoint.offset(-expandBy),InsertionPoint.offset(-1))
set rg=application.intersect(rg,rg.parent.range(colToC heck & ":" &
colToCheck))
rg.formula= "=" & rg.cells(1).offset(-1,0).Address(false,false) & "+1"
rg.copy
rg.pastespecial xlPasteValues
application.cutcopymode=false
'--------------------------------------

Would that work? You may have to adjust a bit this latest code as i haven't
checked it.
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Coal Miner" wrote:

Sebastienm,

That portion works great. Thanks! :)

Is there a way to simply accomplish the other request of assigning the next
sequential # in the 'Work Order' column. See my original message text.

"sebastienm" wrote:

Hi,
The following code looks at last used cell in column A then insert 25 rows
from that point. The Insert should carry thew format of the last row over.

Sub test()
Dim InsertionPoint As Range, rg As Range
Dim colToCheck As String
Dim expandBy As Long
Dim wsh As Worksheet

'---- CHANGE HERE -------
Set wsh = ActiveSheet
colToCheck = "A"
expandBy = 25
'------------------------

'Find last currently used row in column colToCheck
Set InsertionPoint = wsh.Range(colToCheck & 65536).End(xlUp).Offset(1,
0).EntireRow

'Insert rows
Set rg = InsertionPoint.Resize(expandBy)
rg.Insert
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Coal Miner" wrote:

Column 'B' on my worksheet is labeled 'Work Order'. Each row, therefore,
will contain data related to that 'Work Order' #. I would like to create a
macro which will allow the user to hit a button which will add 25 rows to the
worksheet. The rows will be blank other than I would like it to assign the
next consecutive work order # (i.e. if we say we have 100 work orders
assigned, I would like to add 25 rows with work order #'s 101-125). It would
be nice if this code would also somehow copy the formatting of the row. Any
ideas out there??