Help with writing a code

M

Mgoodman

New member
Joined
Dec 20, 2013
Messages
8
Reaction score
0
Points
0
I can't figure out how to write the correct code for a button I have created.

Problem:

I have created a template for bidding jobs. I have formulas inserted from A12:V12 and I want to create a button that when pushed will create another row below the first. I want this row to basically copy all formulas in row 12 and add them to row 13. My hope is that I can create this template and every time a new bid item shows up I can click the "Add" button and a new row with all of my formulas will be created. I have dinked around with the code enough that I can get it to add a row but none of the formulas, lines, or functions transfer down.

Any suggestions on how to remedy this situation would be great, I may also want to mention that I am what some might call just below novice when it comes to excel code!
 
Use the macro recorder to have Excel create the macro for you, copy row 12 up to col V and paste into A13 using the paste formulas option, hit Esc and then stop recording.

This will give you the idea of what you need and something to experiment with.

Any problems or anything with what results, return here explaining the situation and someone will be glad to help.
 
NoS-
This has created a shortcut for copying row 12 so I can easily paste to a new row. When I click the button I've created it just highlights row 12 and enables me to paste into another row. I was looking to make a button that will do all the copy and pasting upon clicking it? Thank you a ton though for your comment this has certainly put me on the right road to cracking this nut.
 
Suspect that you didn't complete what you wanted to do before stopping the macro recorder (like paste the copied cells into A13 and then hit Esc) or the button click would be pasting those formula into row 13. Try again and see if it works for you.
 
Ok that worked... my problem now is every time I click my button it adds a line into row 13. How do I make it add to row 14 then 15 then 16 ETC...? I've tried several times to complete all of these actions and then stop recording, problem being when I click the button it will add several rows rather than just one new one every time.
 
Kinda knew that would be your next question. Have a look at http://www.rondebruin.nl/win/s9/win005.htm and remember that your next row is your last row plus 1. Once you have that number you can replace "A13" in the recorded macro with "A" & LastRow +1 and the macro will use the next row every time it's run.
 
Sub Macro1()
'
' Macro1 Macro
'


'
Range("A12:V12").Select
Selection.Copy
Range("A13").Select
Selection.Insert Shift:=xlDown
Range("B16").Select
Application.CutCopyMode = False
End Sub

What needs to be adjusted to this code? I'm dumber than stupid apparently because I can't figure it out! Thanks again for all of your help.
 
I took Excel generated macro and altered it to this.
Code: 
Sub AddNewRow()
    Dim LastRow As Long
    
With ActiveSheet
    'get last row used in col A
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    'the range to be copied
    .Range("A12:V12").Copy
    'the range to paste into
    .Range("A" & LastRow + 1).PasteSpecial (xlPasteFormulas)
    'stop the "marching ants" around the copied selection
    Application.CutCopyMode = False
    'move the cursor to the start of the new row
    .Range("A" & LastRow + 1).Select
 End With
 
End Sub
 
You're a genius... I am almost there. With that code you just sent it adds a new blank line below but it doesn't insert a line? Below row 12 I have a subtotal, total, % tab. When I click the button now it just covers those lines with new lines. It also isn't adding the box formation of row 12.
 
Sorry Mgoodman, but now I don't quite follow what you're working with.
How about uploading an example of your workbook either by using the Go Advanced button on the lower right of the reply section of this forum or using Skydrive or something and supplying a link to it.

I did wonder why you had used insert and then selected B16. Hope to now find out.
 
Last edited:
Thanks Mgoodman, I'll have a look and get back to ya, but probably won't be until tomorrow.
 
Hi Mgoodman

I think this will work for you but make sure you check out everything.

Don't know why but I had to put a blank row between the "table" and Exclusions and alter your formulas slightly in order to get the totals in col T, U and V to tally.

It wouldn't surprise me to see somebody chime in with some simple way of using tables to accomplish this but for now I hope this will suffice.

Good Luck with your project.
 

Attachments

  • ESTIMATE worksheet_2.xlsm
    94 KB · Views: 13
Mgoodman, I missed hidden column E.

In the macro, after
Code: 
    Application.CutCopyMode = False
add
Code: 
    'copy the value in E12 to inserted row
    .Range("E" & NextRow).Value = .Range("E12").Value
and that should look after it.

Hope this works for you.
NoS
 
Nos I can't thank you enough... I am out in the field today looking at a project but when I get back I will look through this masterpiece you've created. Thanks again for taking the time to help me out!

Marcus
 
Marcus,

You are most welcome, glad I could help, hope this serves your needs.
Any questions, errors or omissions come back and we'll do our best to rectify things.

NoS
 
You must log in or register to reply here.
Back
Top