Automatically insert new rows

[petee]

I have a spreadsheet where I need to copy a row and have EXCEL
automatically copy new rows based on a number inputted by the user
(less 1).

I've used a code which was previously posted that creates a box asking
how many rows I need. This works fine but the problem is that people
often input one row too many
For example: If they need 10 rows overall , they inadvertently input a
10 ... giving a total of 11 rows (the original row + the 10 NEW
rows) )

So I was wondering if anyone would know how to fine tune the code so
that it would give me one less row than what the user inputs.

Here's the code that I use:

Sub copyrows()
numrows = InputBox("Number of rows")
'Rows(ActiveCell.Row).Copy ActiveCell.Resize(numrows)
Rows(ActiveCell.Row).Copy
ActiveCell.Resize(numrows).Insert
Application.CutCopyMode = False
End Sub

Thanks for any Help

 

[Rick Rothstein]

Well, if you can trust them to always enter 2 they want to insert 1 new row,
then simply subtract 1 from the numrows variable's content... if you can't
trust them to do that, then there is no automatic fix available.

Rick Rothstein (MVP - Excel)



"petee" wrote in message

I have a spreadsheet where I need to copy a row and have EXCEL
automatically copy new rows based on a number inputted by the user
(less 1).

I've used a code which was previously posted that creates a box asking
how many rows I need. This works fine but the problem is that people
often input one row too many
For example: If they need 10 rows overall , they inadvertently input a
10 ... giving a total of 11 rows (the original row + the 10 NEW
rows) )

So I was wondering if anyone would know how to fine tune the code so
that it would give me one less row than what the user inputs.

Here's the code that I use:

Sub copyrows()
numrows = InputBox("Number of rows")
'Rows(ActiveCell.Row).Copy ActiveCell.Resize(numrows)
Rows(ActiveCell.Row).Copy
ActiveCell.Resize(numrows).Insert
Application.CutCopyMode = False
End Sub

Thanks for any Help

 

[Donald Guillett]

I have a spreadsheet where I need to copy a row and have EXCEL
automatically copy new rows based on a number inputted by the user
(less 1).

I've used a code which was previously posted that creates a box asking
how many rows I need. This works fine but the problem is that people
often input one row too many
For example: If they need 10 rows overall , they inadvertently input a
10 ... giving a total of 11 rows (the original row + the 10 NEW
rows) )

So I was wondering if anyone would know how to fine tune the code so
that it would give me one less row than what the user inputs.

Here's the code that I use:

Sub copyrows()
numrows = InputBox("Number of rows")
'Rows(ActiveCell.Row).Copy ActiveCell.Resize(numrows)
Rows(ActiveCell.Row).Copy
ActiveCell.Resize(numrows).Insert
Application.CutCopyMode = False
End Sub

Thanks for any Help

Perhaps your code could automatically figure out how many rows to
input based on _____________?
"If desired, send your file to dguillett1 @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."