Add/Remove rows with formatting Macro

[BluOx]

Hello,

I've been trying to piece a macro together from a couple different sites but haven't been able to find a solution for what I'm looking to do. I have an excel doc to track paint emissions per week as well as keep track yearly and monthly totals. I would like to add rows across all 60 some sheets in the spot above the button location while copying all the formatting in the cell above the new row. Additionally I would also like to add a button that will prompt the user for which row they would like to delete and remove it from all sheets.

I certainly appreciate any help, hopefully this will be the last step and I can be done with this document for a while!

~BluOx

 

[mrmmickle1]

Perhaps something like this would work:

[COLOR=#0000ff]Sub[/COLOR] Test()

    [COLOR=#0000ff]Dim [/COLOR]ws [COLOR=#0000ff]As [/COLOR]Worksheet
  [COLOR=#0000ff]  Dim [/COLOR]r  [COLOR=#0000ff]As [/COLOR]Range

    [COLOR=#0000ff]For Each[/COLOR] ws [COLOR=#0000ff]In[/COLOR] ActiveWorkbook.Worksheets
       [COLOR=#0000ff] If[/COLOR] ws.Name <> "Sheet1" [COLOR=#0000ff]Then[/COLOR] [COLOR=#008000] 'Assumes you still want to skip that one sheet...[/COLOR]
      [COLOR=#0000ff]       Set[/COLOR] r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
             r.EntireRow.Insert
    [COLOR=#0000ff]    End If[/COLOR]
   [COLOR=#0000ff] Next [/COLOR]ws
    
[COLOR=#0000ff]End Sub[/COLOR]

 

[BluOx]

It does add rows but all on he master instead of on each individual sheet. Maybe using the button as a reference location isn't the best way to accomplish this?

 

[mrmmickle1]

Try adding this additional code line:

[COLOR=#0000ff]
Sub[/COLOR] Test()

    [COLOR=#0000ff]Dim[/COLOR] ws [COLOR=#0000ff]As [/COLOR]Worksheet
  [COLOR=#0000ff]  Dim[/COLOR] r [COLOR=#0000ff] As[/COLOR] Range

    [COLOR=#0000ff]For Each[/COLOR] ws [COLOR=#0000ff]In [/COLOR]ActiveWorkbook.Worksheets
       [COLOR=#ff0000] ws.Select[/COLOR]
       [COLOR=#0000ff] If[/COLOR] ws.Name <> "Sheet1" [COLOR=#0000ff]Then[/COLOR] [COLOR=#008000] 'Assumes you still want to skip that one sheet...[/COLOR]
             Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
             r.EntireRow.Insert
       [COLOR=#0000ff] End If[/COLOR]
    [COLOR=#0000ff]Next [/COLOR]ws
    
[COLOR=#0000ff]End Sub[/COLOR]

 

[BluOx]

It seems like it want's to do the right thing, but I get a "unable to get the buttons property of the worksheet class" on the first sheet.

 

[mrmmickle1]

It does add rows but all on he master instead of on each individual sheet. Maybe using the button as a reference location isn't the best way to accomplish this?

I'm not absolutely positive but I don't think you can reference a "button" (I assume you mean a command button) location. They usually lie on another layer of the spreadsheet from what I understand. They are like shapes or pictures. You can align them to cells using code like this:

ws.Pictures.Insert ("C:\Your File Directory Here")
                          .Top = Range("A2").Top
                          .Left = Range("A2").Left

but I personally haven't seen it done the opposite. What other marker can you use?

Just a side note: I got the same error you did when I executed the procedure. I just figured it was because I didn't have "buttons" on my spreadsheet.

 

[BluOx]

You may not be able to from another sheet. I know you can reference the button location and add a row above it on the same sheet though. There may be a better way to do it, I'm just not sure what it is or what my options are? Basically what I want the button to do is add a row at the end of the section. So I have 3 sections, Paints, Solvents, Sprays. If we get a new kind the user would need to be able to click the button and it add a row, across all sheets (except sheet 1), below the last row of that section and copy all the formatting.

I'm certainly open for suggestions on better/different ways to do this. The button I had thought would be the easiest way to reference an ever moving location..

If I could attach a document it might be easier to visualize what I'm wanting than what I'm explaining...

 

[mrmmickle1]

BluOx,

I did a little research. Use this instead. I tested it. It should work:

[COLOR=#0000ff]Sub[/COLOR] Test()

    [COLOR=#0000ff]Dim[/COLOR] ws [COLOR=#0000ff]As[/COLOR] Worksheet

   [COLOR=#0000ff] For Each [/COLOR]ws [COLOR=#0000ff]In[/COLOR] ActiveWorkbook.Worksheets
        ws.Select
       [COLOR=#0000ff] If [/COLOR]ws.Name <> "Sheet1" [COLOR=#0000ff]Then [/COLOR] [COLOR=#008000]'Assumes you still want to skip that one sheet..[/COLOR]
            ActiveSheet.CommandButton1.TopLeftCell.EntireRow.Insert [COLOR=#008000] 'This is for ActiveX Control.  Use ActiveSheet.Buttons(1) for Form Control[/COLOR]
[COLOR=#0000ff]        End If[/COLOR]
   [COLOR=#0000ff] Next [/COLOR]ws
    
[COLOR=#0000ff]End Sub[/COLOR]

 

[BluOx]

After receiving a 'unable to get the buttons property of the worksheet class error' I changed it to Button(18), which it's supposed name, but now I receive a 'Object doesn't support this property or method' error. I also noticed it says you're using excel 2010/13, I'm on 2007 could it be the command isn't supported in 2007?

 

[mrmmickle1]

For this method to work all of the Buttons where the row is to be inserted on separate sheets will need to be named the same thing (i.e.. In the above example all buttons would need to be button 1 on their respective sheets). If you are having trouble using this method then try naming all of the buttons the same thing by using this method:

1. Select the Button.
2. Open The VBE (Alt + F11)
3. Use key Combination Ctrl + G to display the immediate window
4. Type this line of code in the immediate window

selection.name = "MyButtonName"

5. Then Hit Enter

This will allow you to rename the buttons the same name on different sheets. Then use the following code:

[COLOR=#0000ff]Sub [/COLOR]Test()

   [COLOR=#0000ff] Dim[/COLOR] ws [COLOR=#0000ff]As [/COLOR]Worksheet

    [COLOR=#0000ff]For Each [/COLOR]ws [COLOR=#0000ff]In [/COLOR]ActiveWorkbook.Worksheets
        ws.Select
        [COLOR=#0000ff]If[/COLOR] ws.Name <> "Sheet1" [COLOR=#0000ff]Then [/COLOR][COLOR=#008000] 'Assumes you still want to skip that one sheet..[/COLOR]
            ActiveSheet.Buttons("MyButtonName").TopLeftCell.EntireRow.Insert [COLOR=#008000] 'This is For a Form Control[/COLOR]
[COLOR=#0000ff]        End If[/COLOR]
  [COLOR=#0000ff]  Next[/COLOR] ws
    
[COLOR=#0000ff]End Sub[/COLOR]

I was unable to duplicate the error you mentioned, but hopefully this will help to get you sorted!

 

[BluOx]

Would this solution require a button on each sheet? Currently the only buttons are located on the master tab, which I prefer over having a button on each...

I apologize if I wasn't clear on this fact to begin with.

Also, I don't see where this code copies the formula's from the row above where it's inserted?