Looping Issue

[bridgetd15]

I have an excel sheet that many people will be using so it is locked. I am trying to add a button to add lines in a section. I have the button working to add a line but when you hit the button again, it does it for the same row. I'd like it to add a row at the bottom of the section each time so the first row is inserted at row 12 and the next row would be inserted in row 13 then row 14 and so on (hope this makes sense). The inserted row is then copied from the row above it to include the formulas. Below is what is written for the button (I used google for help to get to this point as I haven't worked in VBA in many years).

Private Sub AddLaborLine_Click()
Application.ScreenUpdating = False
Dim xSheet As Worksheet
Set xSheet = ActiveSheet
If xSheet.Name <> "Definitions" And xSheet.Name <> "fx" And xSheet.Name <> "Needs" Then
xSheet.Range("A12:R12").Insert
xSheet.Range("A11:R11 ").Copy
xSheet.Range("A12:R12").PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
Application.ScreenUpdating = True

End Sub

I have tried adding Loop, Next, and anything else I could find to possibly help on google. I've tried so many different things and can't get it to work. Is this possible to do? If so, what do I need to do to accomplish it? This is just the code for the button to work on the same line. I didn't include anything I've tried to try to loop it to the next line.
Thanks!

 

[Joe4]

Welcome to the Board!

Try this:

Private Sub AddLaborLine_Click()

    Application.ScreenUpdating = False

    Dim xSheet As Worksheet
    Dim lr As Long

    Set xSheet = ActiveSheet

    If xSheet.Name <> "Definitions" And xSheet.Name <> "fx" And xSheet.Name <> "Needs" Then
'       Find last row with data in column A
        lr = xSheet.Cells(Rows.Count, "A").End(xlUp).Row
        xSheet.Range("A" & lr + 1 & ":R" & lr + 1).Insert
        xSheet.Range("A" & lr & ":R" & lr).Copy
        xSheet.Range("A" & lr + 1 & ":R" & lr + 1).PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End If

    Application.ScreenUpdating = True

End Sub

 

[bridgetd15]

Welcome to the Board!

Try this:

Private Sub AddLaborLine_Click()

    Application.ScreenUpdating = False

    Dim xSheet As Worksheet
    Dim lr As Long

    Set xSheet = ActiveSheet

    If xSheet.Name <> "Definitions" And xSheet.Name <> "fx" And xSheet.Name <> "Needs" Then
'       Find last row with data in column A
        lr = xSheet.Cells(Rows.Count, "A").End(xlUp).Row
        xSheet.Range("A" & lr + 1 & ":R" & lr + 1).Insert
        xSheet.Range("A" & lr & ":R" & lr).Copy
        xSheet.Range("A" & lr + 1 & ":R" & lr + 1).PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End If

    Application.ScreenUpdating = True

End Sub

That is getting me really close but I have 5 different sections I need to add the button for. They all have different formulas. The first section has a heading LABOR in A6 with rows A7-A11 under that section. The next section starts on A12 with MATERIAL. I need these formulas just added to this specific section. I tried to make it work with what you provided above and cant seem to quite figure it out.
Thank you SO much for your help!

 

[Joe4]

So, when you click this button, do you want it to insert a new row at the bottom of each section?
How many of these sections are there?
Are there any sections which should NOT have a new row inserted?

Please post a sample of your data, showing these different sections, so we can see exactly how your data is structured.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[bridgetd15]

So, when you click this button, do you want it to insert a new row at the bottom of each section?
How many of these sections are there?
Are there any sections which should NOT have a new row inserted?

Please post a sample of your data, showing these different sections, so we can see exactly how your data is structured.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I plan to make a button for each section so I just need the row inserted at the bottom of the current section. I'll repeat the code and create a new button for each section. They all have different formulas and link to other sheets in the workbook. I have included a screenshot of the set up if that helps. If that doesn't work, I can use the XL2BB tool.
Again, thank you so much for your help.

 

[Joe4]

OK, we need to determine which column we can use to determine where each section ends. So, there would be a formula in the last row, followed by a blank (i.e. the header row for the next sections).
Which column will ALWAYS have formulas in, no matter which section it is?
It looks like it might be column K, but I cannot be certain, as you did not post column headers in your image.

 

[bridgetd15]

OK, we need to determine which column we can use to determine where each section ends. So, there would be a formula in the last row, followed by a blank (i.e. the header row for the next sections).
Which column will ALWAYS have formulas in, no matter which section it is?
It looks like it might be column K, but I cannot be certain, as you did not post column headers in your image.

Yes column K

 

[Joe4]

OK. I don't think it matters if the formulas change, since we are just copying the formulas from the row above.
I think we can make one "base-line" code that you use for ALL your buttons.
That code would look something like this:

Private Sub MyInsertRow(sectionName As String)

    Dim r As Long
    Dim lr As Long

'   Find row with desired header
    On Error GoTo err_chk:
    r = Columns("A:A").Find(What:=sectionName, After:=Range("A1"), LookIn:=xlFormulas2, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Row
    On Error GoTo 0
    
'   Find last row in section
    lr = Cells(r + 1, "K").End(xlDown).Row
    
'   Insert new rows
    Rows(lr + 1).Insert
    
'   Copy formulas down to new row
    Range("A" & lr & ":R" & lr).Copy
    Range("A" & lr + 1 & ":R" & lr + 1).PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

    Exit Sub
    
err_chk:
    If Err.Number = 91 Then
        MsgBox "No header with title of " & sectionName & " found in column A.", vbOKOnly, "ERROR!"
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If

End Sub

Note how this code has a parameter, specifically the section name that we are looking for.
So for you "Add Labor Line" button, this is the code you would have connected to it:

Private Sub AddLaborLine_Click()
    Call MyInsertRow("LABOR")
End Sub

So all your other buttons would look virtually the same, except they would have a different value between the quotes.
That is how you can efficiently re-use code, have all your buttons call the same code, just with a different parameter we are feeding in to it.

 

[bridgetd15]

OK. I don't think it matters if the formulas change, since we are just copying the formulas from the row above.
I think we can make one "base-line" code that you use for ALL your buttons.
That code would look something like this:

Private Sub MyInsertRow(sectionName As String)

    Dim r As Long
    Dim lr As Long

'   Find row with desired header
    On Error GoTo err_chk:
    r = Columns("A:A").Find(What:=sectionName, After:=Range("A1"), LookIn:=xlFormulas2, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Row
    On Error GoTo 0
   
'   Find last row in section
    lr = Cells(r + 1, "K").End(xlDown).Row
   
'   Insert new rows
    Rows(lr + 1).Insert
   
'   Copy formulas down to new row
    Range("A" & lr & ":R" & lr).Copy
    Range("A" & lr + 1 & ":R" & lr + 1).PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

    Exit Sub
   
err_chk:
    If Err.Number = 91 Then
        MsgBox "No header with title of " & sectionName & " found in column A.", vbOKOnly, "ERROR!"
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If

End Sub

Note how this code has a parameter, specifically the section name that we are looking for.
So for you "Add Labor Line" button, this is the code you would have connected to it:

Private Sub AddLaborLine_Click()
    Call MyInsertRow("LABOR")
End Sub

So all your other buttons would look virtually the same, except they would have a different value between the quotes.
That is how you can efficiently re-use code, have all your buttons call the same code, just with a different parameter we are feeding in to it.

That worked!!!! I cannot thank you enough for your help!!!

 

[Joe4]

You are welcome.
Glad I was able to help!