Add/Remove rows with formatting Macro

B

BluOx

New Member
Joined
Aug 18, 2014
Messages
42
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
 
Sort by date Sort by votes
If you are only trying to insert a row on one worksheet then there is no need to cycle through all of the worksheets in the workbook. You can just use something like this. It will copy the row above the command button, it's formatting, and its formulas and then insert a new row below that original row and therefore shift the command button down.

Here is the Code:

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

[COLOR=#0000ff]     Dim [/COLOR]CopyRow [COLOR=#0000ff]As Integer[/COLOR]

        Sheets("Master").Select[COLOR=#008000]  'If you are already on the Master Sheet this line of code can be deleted[/COLOR]
        ActiveSheet.Buttons("MyButtonName").TopLeftCell.Select
        CopyRow = ActiveCell.Row - 1
        Rows(CopyRow).Copy
        Rows(CopyRow + 1).Insert Shift:=xlDown

[COLOR=#0000ff]End Sub[/COLOR]
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
I would like it to insert a row, with formatting on all the sheets (except sheet1) but the only place the button's will be is on the master sheet. Does that make sense?

So if a user needs to add another paint coating they would go to the master tab, click the add additional paint coatings button, and it will insert a row in the same location across all the sheets. Then they populate the information on the master tab and it replicates (via formulas on the other sheets) the information to each sheet.

Is there a way to attach a picture or a copy of the document? It might be easier to see it rather than for me to try to explain it...
 
Upvote 0
Try this. Assuming your ActiveSheet is "Master", it will insert rows on all other sheets based on the position of the form control button on the "Master" Tab:

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

  [COLOR=#0000ff]  Dim[/COLOR] wksht  [COLOR=#0000ff] As [/COLOR]Worksheet
    [COLOR=#0000ff]Dim[/COLOR] CopyRow [COLOR=#0000ff]As Integer[/COLOR]
  [COLOR=#0000ff]  Dim[/COLOR] r      [COLOR=#0000ff] As [/COLOR]Range

    ActiveSheet.Buttons("MyButtonName").TopLeftCell.Select
    CopyRow = ActiveCell.Row - 1

   [COLOR=#0000ff] For Each [/COLOR]wksht [COLOR=#0000ff]In[/COLOR] ActiveWorkbook.Worksheets
       [COLOR=#0000ff] If [/COLOR]wksht.Name <> "Master" [COLOR=#0000ff]Then[/COLOR]
          [COLOR=#0000ff] With [/COLOR]wksht
             .Select
             .Rows(CopyRow).Copy
             .Rows(CopyRow + 1).Insert Shift:=xlDown
[COLOR=#0000ff]           End With[/COLOR]
[COLOR=#0000ff]        End If[/COLOR]
  [COLOR=#0000ff]  Next[/COLOR] wksht
    
[COLOR=#0000ff]End Sub[/COLOR]
Not sure I understand the formula portion. What cells or range on the Master Sheet are they entering information in? What cells/range on the additional sheets are supposed to contain these values?

If this still doesn't work you can post an example workbook to a file sharing site like DropBox, FileSnack, etc...
 
Last edited:
Upvote 0
Ooo I think we're close! I modified your code just a tad but it now wraps across all the sheets and enters a row above the location where the button was clicked, which is exactly what I was looking for. The only issue I've come across is it doesn't expand the table, but does copy the code I was wanting. So if the worst case scenario is I have to remove the table from each sheet and just have colored rows, I can live with that...

Code: 
Dim wksht      As Worksheet
Dim CopyRow As Integer
Dim r             As Range

Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
CopyRow = ActiveCell.Row - 1

For Each wrksht In ActiveWorkbook.Worksheets
          With wksht
              .Select
              .Rows (CopyRow).Copy
              .Rows (CopyRow + 1).INSERT Shift:=xldown
           End With
           Next wksht

You're a genius! I am so very thankful for your help!! (y) (y)
 
Upvote 0
To answer the second part of your questions basically all my sheets are laid out the same: Col A has a part #, B has a coating name, C-I are where they enter the amount of paint used (displayed in a table), J totals what they entered for the week, K is the amount of emissions that particular paint has and L multiplies J &K.

So all the data listed in A,B,&K is driven from the master sheet. The Yearly totals sheets pulls all the data from the different tables and totals it up nicely so we can make sure we're not going over EPA/DNR allotments. I also then have month sheets which pull in all the data for the month for the different weeks so I can track monthly usage as well. It's really just a giant pain in the @$$....
 
Upvote 0
BluOx,

This should do what you want. You will need to rename your tables generically by going to the Design Tab on each sheet. Rename Each Table like this: Table1, Table2, Table3 etc... In the below sub procedure i will represent 1, 2, 3 as each sheet is cycled through. Use F8 to step through the code one line at a time and see what is happening. It will help you to understand what's going on. You can hover over variables like i with your mouse to see their values....

Note this assumes the Table on each worksheet exists in columns A:L. The number 12 Represents Column L in the below code. Change this reference if necessary. (M would be 13, N would be 14, etc..)

Here you go:

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

   [COLOR=#0000ff] Dim[/COLOR] wksht    [COLOR=#0000ff]As[/COLOR] Worksheet
   [COLOR=#0000ff] Dim[/COLOR] CopyRow  [COLOR=#0000ff]As Integer[/COLOR]
    [COLOR=#0000ff]Dim[/COLOR] LastRow  A[COLOR=#0000ff]s Long[/COLOR]
    [COLOR=#0000ff]Dim[/COLOR] i       [COLOR=#0000ff] As Integer[/COLOR]

    Sheets("Master").Select [COLOR=#008000] 'If you are already on the Master Sheet this line of code can be deleted[/COLOR]
    ActiveSheet.Buttons("MyButtonName").TopLeftCell.Select
    CopyRow = ActiveCell.Row - 1 [COLOR=#008000]'Get Row Location[/COLOR]

   [COLOR=#0000ff] For[/COLOR] [COLOR=#0000ff]Each [/COLOR]wksht [COLOR=#0000ff]In[/COLOR] ActiveWorkbook.Worksheets
      
     [COLOR=#0000ff]   If [/COLOR]wksht.Name <> "Master" [COLOR=#0000ff]Then[/COLOR]
           i = i + 1  
           wksht.Activate
           LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
          [COLOR=#0000ff] With[/COLOR] wksht
                .ListObjects("Table" & i).Resize Range("A1:L" & LastRow + 1)[COLOR=#008000] 'Resize Table[/COLOR]
                .Range(wksht.Cells(LastRow, 1), wksht.Cells(LastRow, 12)).Copy
                .Range(wksht.Cells(LastRow + 1, 1), wksht.Cells(LastRow + 1, 12)).PasteSpecial
[COLOR=#0000ff]           End With[/COLOR]
[COLOR=#0000ff]        End If[/COLOR]

  [COLOR=#0000ff]  Next [/COLOR]wksht

[COLOR=#0000ff]End Sub[/COLOR]
 
Upvote 0
I had to make a few changes, but because Col A, B, J, K, and L aren't part of the table it really doesn't like it and gives me an error about trying to add a row inside a table and outside. Honestly the only real reason I have a table at all is for the banded rows shading. If this could be accomplished another way I would be happy to get rid of them and use the code that I spoke about earlier that gets us pretty close.

The only issues I've found with that one is if I don't select the area above the button it inserts the rows in that location. I also noticed it copies the formatting, but also any data the user may have entered is it possible to separate the two? The data is always going to be entered in the same columns and only certain pages have formatting in those locations. Maybe something like if named sheet then copy row above and insert, else if numbered sheet insert entire row and copy Col A,B,J,K,L?

I really do appreciate all your help. I'm sure doing it this way is quite frustrating on your end, but I'm closer than I've been in months to getting this thing sorted out!

This is the code I was referring to:
Code: 
  Dim wksht   As Worksheet
    Dim CopyRow As Integer
    Dim r       As Range


    Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
    CopyRow = ActiveCell.Row - 1


    For Each wksht In ActiveWorkbook.Worksheets
           With wksht
             .Select
             .Rows(CopyRow).Copy
             .Rows(CopyRow + 1).INSERT Shift:=xlDown
           End With
           Next wksht
 
Upvote 0
Try this it will insert the alternating colors based on data length:
Code: 
[COLOR=#0000ff]Sub[/COLOR] Test()

   [COLOR=#0000ff] Dim[/COLOR] wksht  [COLOR=#0000ff]  As [/COLOR]Worksheet
  [COLOR=#0000ff]  Dim[/COLOR] CopyRow[COLOR=#0000ff]  As Integer[/COLOR]
    [COLOR=#0000ff]Dim[/COLOR] lastRow  [COLOR=#0000ff]As Long[/COLOR]
  [COLOR=#0000ff]  Dim [/COLOR]Cell     [COLOR=#0000ff]As [/COLOR]Range

    ActiveSheet.Buttons("MyButtonName").TopLeftCell.Select
    CopyRow = ActiveCell.Row - 1

[COLOR=#0000ff]    For Each [/COLOR]wksht [COLOR=#0000ff]In[/COLOR] ActiveWorkbook.Worksheets
       [COLOR=#0000ff] If [/COLOR]wksht.Name <> "Master" [COLOR=#0000ff]Then[/COLOR]
           wksht.Activate
           lastRow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
               
        [COLOR=#0000ff]   With [/COLOR]wksht
             .Select
             .Rows(CopyRow).Copy
             .Rows(CopyRow + 1).Insert Shift:=xlDown
[COLOR=#0000ff]           End With[/COLOR]
           
          [COLOR=#0000ff] For Each [/COLOR]Cell I[COLOR=#0000ff]n [/COLOR]Range("A1:F" & lastRow) [COLOR=#008000]'Change Range Accordingly[/COLOR]
               [COLOR=#0000ff] If [/COLOR]Cell.Row Mod 2 = 1 [COLOR=#0000ff]Then[/COLOR]
                  Cell.Interior.ColorIndex = 15 [COLOR=#008000]'Change Color 15 = Grey[/COLOR]
[COLOR=#0000ff]                Else[/COLOR]
                  Cell.Interior.ColorIndex = xlNone
[COLOR=#0000ff]                End If[/COLOR]
                
         [COLOR=#0000ff]  Next [/COLOR]Cell
                
[COLOR=#0000ff]        End If[/COLOR]
         
    [COLOR=#0000ff]Next[/COLOR] wksht
    
[COLOR=#0000ff]End Sub[/COLOR]

Not sure what you mean by copying data. I thought there were formulas in those cells?? When I execute the procedure it copies the formulas down and not the values.
 
Upvote 0
Correct. I have formula's on all sheets (except the first and master) for Columns A, B, J, K, and L. On the numbered sheets the user inputs data in the table, the month sheets then have a formula to add all the data for a specific month so those tables are nothing but formulas.

It's kind of confusing to explain so I'm sure it's even harder to understand when you're not looking at it.
https://www.dropbox.com/s/niupf11jcghuxxn/Internet%20Help.xlsm
 
Upvote 0
Try something like this. It will just insert formatting on all sheets based on Criteria collected in InputBoxes. It may be simpler then the other method we were going with:

Code: 
[COLOR=#0000ff]Sub[/COLOR] AddTableRow()

[COLOR=#0000ff]Dim[/COLOR] RowNumber [COLOR=#0000ff]As Integer[/COLOR]
[COLOR=#0000ff]Dim[/COLOR] RowColor  [COLOR=#0000ff]As Integer[/COLOR]

RowNum = Application.InputBox(Prompt:="Select The Row Number to Format", Title:="Format Tables", Type:=1)
RowColor = Application.InputBox(Prompt:="Type 1 for Blue Or 2 For White", Title:="Format Tables", Type:=1)

Application.ScreenUpdating = [COLOR=#0000ff]False[/COLOR]

[COLOR=#0000ff] For Each [/COLOR]wksht[COLOR=#0000ff] In[/COLOR] ActiveWorkbook.Worksheets
       [COLOR=#0000ff] If[/COLOR] wksht.Name <> "Master" [COLOR=#0000ff]Then[/COLOR]
           wksht.Activate
           
          [COLOR=#0000ff] With [/COLOR]wksht
                If RowColor = 1 [COLOR=#0000ff]Then[/COLOR]
                        .Range("C" & RowNum & ":I" & RowNum).Select
                        Selection.Interior.ColorIndex = 20
                        Selection.Borders(xlEdgeRight).Color = -4165632
                        Selection.Borders(xlEdgeLeft).Color = -4165632
                        Selection.Borders(xlEdgeTop).Color = -4165632
                        Selection.Borders(xlEdgeBottom).Color = -4165632             
[COLOR=#0000ff]                 Else[/COLOR]
                        .Range("C" & RowNum & ":I" & RowNum).Select
                        Selection.Interior.ColorIndex = 2
                        Selection.Borders(xlEdgeRight).Color = -4165632
                        Selection.Borders(xlEdgeLeft).Color = -4165632
                        Selection.Borders(xlEdgeTop).Color = -4165632
                        Selection.Borders(xlEdgeBottom).Color = -4165632
[COLOR=#0000ff]                End If[/COLOR]
[COLOR=#0000ff]            End With[/COLOR]
[COLOR=#0000ff]        End If[/COLOR]
    [COLOR=#0000ff]Next [/COLOR]wksht

Application.ScreenUpdating = [COLOR=#0000ff]True[/COLOR]

[COLOR=#0000ff]End Sub[/COLOR]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Macro to add sum total to track percentages
Replies
0
Views
122
spenserallen7
S
C
  • Question
Delete Cell Range, or Add Range and Copy Formulas etc. - Excel VBA
Replies
0
Views
98
CSTjr
C
D
  • Question
Form Control to open a window or form to add a list of numbers, perform a calculation and paste value to a selected cell.
Replies
0
Views
97
DaveCutting
D
W
  • Solved
Row Transfer Macro
Replies
9
Views
117
mumps
mumps
E
  • Solved
Add row to table copying formatting
Replies
3
Views
279
skillilea
S

Forum statistics

Threads
1,215,510
Messages
6,125,237
Members
449,217
Latest member
Trystel

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top