Duplicate Command Button

[Bkisley]

Is there a way I can create a command button and then copy and paste that button several more times to other areas on the same tab without having to redo the code each time?

I created a command button that when clicked does the following
Insert row below this row
removes format from the row above
carries down formula from the row above

I need to do this process 29 more times. When I copy and paste this button down to the next section, (2 of the total 30 times) it does the correct process, however it goes back to the row where the first button began. I need this process to start over at the row each button is.

The other issue I am running into is when I click on the command button to do the process shown above, when I click on that same button again it inserts a row directly below the first row thus pushing down my new data down a row. I want a row inserted below the previously inserted row from the first click

Please Help!
Thanks

 

[AFPathfinder]

I think it would become a little unwieldy to have that many buttons that do the same thing when you can have one button do multiple rows. I had something similar that performed an action on a row that was currently selected. What if you just had a single button at the top of the page and the it would insert a row below the currently selected cell? Or, if it's just to extend the table by another row (always adding to the bottom), then that would be even easier with a single button.

 

[Bkisley]

If one button could do that based on where a cell is selected that would be fantastic!

 

[AFPathfinder]

Can you post the existing code to this thread? Don't forget to use code tags for the VBA (The # button in the settings bar of the forum's "Your Message" window.

 

[Bkisley]

Sub onedown()
'
' onedown Macro
'


'
Rows("11:11").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("L11").Select
Selection.FillDown
Range("O11").Select
Selection.FillDown
Range("I11").Select
End Sub

 

[AFPathfinder]

Give this one a shot and see how it work. It will add a new row on the row right below the cell you have selected.

Sub onedown()
    Dim rw As Integer
    Dim lastCol As Integer
    
    rw = Selection.Row + 1
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    Rows(i).Insert CopyOrigin:=xlFormatFromLeftOrAbove
    
    With Cells(i, 1).Resize(, lastCol)
        .Interior.ThemeColor = xlThemeColorDark1
        .Borders.LineStyle = xlNone
    End With
    
    Range("L" & i).FillDown
    Range("O" & i).FillDown
    Range("I" & i).Select
End Sub

If it doesn't do exactly as you like, we can adjust it as needed.

 

[Bkisley]

When I do this I get the following issue in the VBA code highlighted

Rows(i).Insert CopyOrigin:=xlFormatFromLeftOrAbove

I do not know why that is happening

 

[AFPathfinder]

Bah! Sorry about that. I was starting to use "i" as the variable before I changed it to "rw", but never changed the rest of the references. This one should work for you:

Sub onedown()
    Dim rw As Integer
    Dim lastCol As Integer
    
    rw = Selection.Row + 1
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    Rows(rw).Insert CopyOrigin:=xlFormatFromLeftOrAbove
    
    With Cells(rw, 1).Resize(, lastCol)
        .Interior.ThemeColor = xlThemeColorDark1
        .Borders.LineStyle = xlNone
    End With
    
    Range("L" & rw - 1).FillDown
    Range("O" & rw - 1).FillDown
    Range("I" & rw).Select
End Sub

 

[Bkisley]

Oh you are getting so close!
When I do this for the first row it works exactly as it should, however there is one issue I am running into.
The new row that is being inserted does not use the above formulas in column L and O.

On a side note but still relevant to this, is there a way that after I click the button to add in a row I can go back and undo something. Everytime I click the button I lose the ability to undo. This could be a huge issue.

 

[AFPathfinder]

I'm not too familiar with the Range.FillDown function, so I guess that was an oversight. Let's try this version and see if it fixes that formula issue.

Sub onedown()
    Dim rw As Integer
    Dim lastCol As Integer
    
    rw = Selection.Row + 1
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    Rows(rw).Insert CopyOrigin:=xlFormatFromLeftOrAbove
    
    With Cells(rw, 1).Resize(, lastCol)
        .Interior.ThemeColor = xlThemeColorDark1
        .Borders.LineStyle = xlNone
    End With
    
    Range("L" & rw).FormulaR1C1 = Range("L" & rw - 1).FormulaR1C1
    Range("O" & rw).FormulaR1C1 = Range("O" & rw - 1).FormulaR1C1
    Range("I" & rw).Select
End Sub

For undo, this is much more difficult. VBA changes do not save the previous state so a user cannot just Undo like they normally would. For something like this, we could have a makeshift undo by recording the rows that were added in the first macro and deleting those rows when the user presses another button for a second macro. For example, if the first macro inserts a new row in row 11, the macro can also input an "11" into a separate column or sheet so it can remember the last row that was added. The second macro could go to that separate column or sheet, read the last row number, then delete the row on the main sheet.