Macro to insert lines

[tanyhart]

I have a macro that will insert my selected lines underneath the las
entry. However, I can only get it to do this once, and in the sam
spot. How can I get it to keep adding lines after each entry if th
user needed to?

For example if I pressed the command button once, it would enter th
new set of lines and renumber it. If I wanted to enter more line
after this entry, I would press the button again and it would enter th
new blank lines and renumber in sequence. This is the macro
recorded.


Rows("10:12").Select
Selection.Copy
Range("A19").Select
ActiveSheet.Paste
Range("B16:B18").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("B16:B21")
Type:=xlFillDefault
Range("B16:B21").Select

Can anyone help me out?

Thanks

 

[mkerstei]

I'm not quite sure what you are asking, but I think this will at least
help.
For now I filled cells A1:A20 with the numbers 0-20. I then added this
line to your macro:

Range("A1").End(xlDown).Select

So your new macro looks like this:

Sub Bleh()
Rows("10:12").Select
Selection.Copy
Range("A1").End(xlDown).Select
ActiveSheet.Paste
Range("B16:B18").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("B16:B21"), Type:=xlFillDefault
Range("B16:B21").Select
End Sub

If that's not what you're looking for, I think it will at least give
you a good start finding it.

 

[Don Guillett]

let's start by cleaning up what you have to this.

Rows("10:12").Copy Range("A19")
Range("B16:B18").AutoFill Destination:=Range("B16:B21")

Now, explain what you want next

 

[tanyhart]

If you look at the example I have in a Word file, the 3 rows that star
with "Task #13" are the ones that were inserted with the macro create
above.

What I would like to do now, is be able to enable the user to hit tha
button again and have more rows inserted below Task #13 that would be
continuation for Task #14, #15, and so forth. It would be dependen
upon how many tasks each estimate needs, so there would never be an
set number of additions to the tasks. Is the able to be done

+-------------------------------------------------------------------
|Filename: Example.doc
|Download: http://www.excelforum.com/attachment.php?postid=4864
+-------------------------------------------------------------------

 

[Don Guillett]

In the ms ng I do not see these php files.

 

[tanyhart]

Sorry, I have no clue what your message meant. In the word file is
picture of what the spreadsheet looks like

 

[Don Guillett]

Sorry, I have no clue what is in your attachment since we don't get the
attachments in this ng. Perhaps you should try copy\paste and clear
explanations if you desire help here.

 

[tanyhart]

The macro you cleaned up for me works great, what I would like it t
further accomplish is to continue to enter more lines and renumber the
in sequence based upon the amount of line the user requires.

Right now it only enters three lines and renumbers it to the next #.
need it to keep doing that further down the sheet each time the use
hits it

 

[Don Guillett]

You really DO need to give an example. You may send the workbook to my
personal email below along with COMPLETE and DETAILED instructions on what
you need. I will not make an attempt to read your mind.

 

[tanyhart]

Thanks again for your help.

When I enter the Code into my master file, I get a run-time error '13'
Type mismatch on the line beginning mynum =

Sub addtasks()
Application.DisplayAlerts = False
myrow = Cells.Find("Total P&C Estimate").Row - 3
mycell = Cells(myrow, 2)
mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1

With Range(Cells(myrow, 2), Cells(myrow + 2, 2))
EntireRow.Copy
EntireRow.Insert Shift:=xlDown
End With

Application.CutCopyMode = False
Cells(myrow + 3, 2) = "Task#" & mynum
Application.DisplayAlerts = True
End Sub

 

[tanyhart]

I figured it out.

Thank

 

[Don Guillett]

I didn't see your master file. As you know, it worked fine on the file you
sent to me.
I also notice here that you left out the . 's in the with statement.

Task #12

 

[tanyhart]

I worked on it and got it figured out, I had an extra row in m
spreadsheet. When I deleted it, the code worked.

Thank