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
I also can't seem to figure out how to use the below code to only add a bottom bored to columns C - I...
Code: 
Application.ScreenUpdating = False 'Turns screen updating on or off
    On Error Resume Next 'This will make the code bypass the error message
    Dim uRow As Integer
    uRow = InputBox("Please type the row number to be deleted") 'Get the user to input the row to delete
    If uRow = 0 Then Exit Sub
    For i = 2 To ThisWorkbook.Sheets.Count 'Cycles through each sheet
    ThisWorkbook.Sheets(i).Rows(uRow).Delete
    ThisWorkbook.Sheets(i).Rows(uRow - 1).Borders(xlEdgeBottom).Color = 0 'Add borders to bottom of deleted row
   Next i
Application.ScreenUpdating = True

I can get it to add a border across all the entire row or not at all...
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
BluOx,

1. If your Formulas are not absolute (i.e. =$A$24) then they will copy relatively. (Like when you Autofill)
2. Change the code to this:

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

Application.ScreenUpdating = [COLOR=#0000ff]False[/COLOR] [COLOR=#008000]'Turns screen updating on or off[/COLOR]
   [COLOR=#0000ff] On Error Resume Next[/COLOR] [COLOR=#008000]'This will make the code bypass the error message[/COLOR]
    [COLOR=#0000ff]Dim [/COLOR]uRow [COLOR=#0000ff]As Integer[/COLOR]
    uRow = InputBox("Please type the row number to be deleted") 'Get the user to input the row to delete
[COLOR=#0000ff]    If [/COLOR]uRow = 0 [COLOR=#0000ff]Then Exit Sub[/COLOR]
  [COLOR=#0000ff]  For[/COLOR] i = 2 [COLOR=#0000ff]To[/COLOR] ThisWorkbook.Sheets.Count [COLOR=#008000]'Cycles through each sheet[/COLOR]
    ThisWorkbook.Sheets(i).Rows(uRow).Delete
[COLOR=#ff0000]    ThisWorkbook.Sheets(i).Range(Cells(uRow - 1, 3), Cells(uRow - 1, 9)).Borders(xlEdgeBottom).Color = 0 [/COLOR][COLOR=#008000]'Add borders to bottom of deleted row[/COLOR]
 [COLOR=#0000ff]  Next[/COLOR] i
Application.ScreenUpdating = [COLOR=#0000ff]True[/COLOR]

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

You probably need to add this to the end of the procedure as well:

Code: 
[COLOR=#0000ff]On Error GoTo 0[/COLOR][COLOR=#008000] 'Resets Error Handling to Default[/COLOR]

Otherwise, it's possible that code in other modules inadvertently suppresses errors that you may want to see....

Error Handling Link
 
Upvote 0
I'm not sure I understand what you mean regarding the absolute formulas. If it were to copy similar to autofill I would be fine with that, but what's happening is that the formulas in columns A, B and K are off by one row. The rest of the columns, C-J and L are correct.

For example when adding row 23, it inserts, shifts and copies exactly what I want, except the formula reads =IF(Master!B24="","", Master!B24) instead of =IF(Master!B23="","",Master!B23). If I fix the formula and again insert a row it goes to 25 instead of 24 like it's supposed to. I took out the IF statement (though it's the same format that is used in the table able it copies fine) but it does the same thing.
 
Last edited:
Upvote 0
Try changing the original formula in that particular cell to this:
Code: 
[COLOR=#333333]=IF(Master!$B$23="","",Master!$B$23)[/COLOR]

Then try running the code again.
 
Upvote 0
With that formula it copies it exactly on the next line instead of being one higher. So it's copying line 22 which is =IF(Master!$B$22 ="", "", Master!$B$22) and that's what's inserted on line 23 as well.
 
Upvote 0
Absolutely, it's the one you figured out for me earlier in the week (last week?). Like I said, I don't understand why it works for some of the cells but adds a row in some others...

Code: 
Dim RowNumber As Integer
Dim RowColor  As Integer


Rownum = Application.InputBox(Prompt:="Enter Row number for new item", Title:="Format Tables", Type:=1)
If Rownum = 0 Then Exit Sub
RowColor = Application.InputBox(Prompt:="Type 1 for Blue Or 2 For White", Title:="Format Tables", Type:=1)
Application.ScreenUpdating = False


 For Each wksht In ActiveWorkbook.Worksheets
        If wksht.Name <> "Instructions" Then
           wksht.Activate
            With wksht
               Rows(Rownum).Select
               Selection.EntireRow.INSERT
            
                 For i = 1 To 12 Step 1
                     If Cells(Rownum - 1, i).HasFormula Then
                         Cells(Rownum - 1, i).Copy Destination:=Cells(Rownum, i)
                     End If
                 Next i
                 
                 If RowColor = 1 Then
                         .Range("C" & Rownum & ":I" & Rownum).Select
                         Selection.Interior.Color = RGB(219, 229, 241) 'Colors rows blue
                         Selection.Borders(xlEdgeRight).Color = 0
                         Selection.Borders(xlEdgeLeft).Color = 0
                         Selection.Borders(xlEdgeTop).Color = 0
                         Selection.Borders(xlEdgeBottom).Color = 0
                         Selection.Borders(xlInsideHorizontal).Color = 0
                         Selection.Borders(xlInsideVertical).Color = 0
                 Else
                         .Range("C" & Rownum & ":I" & Rownum).Select
                         Selection.Interior.ColorIndex = 2  'Colors rows white
                         Selection.Borders(xlEdgeRight).Color = 0
                         Selection.Borders(xlEdgeLeft).Color = 0
                         Selection.Borders(xlEdgeTop).Color = 0
                         Selection.Borders(xlEdgeBottom).Color = 0
                         Selection.Borders(xlInsideHorizontal).Color = 0
                         Selection.Borders(xlInsideVertical).Color = 0
                 End If
             End With
        End If
    Next wksht


Application.ScreenUpdating = True
 
Upvote 0
BluOx,

If I understand correctly. If a table goes through Row 22 then the formula in this row will be:
Code: 
=IF(Master!B22="","",Master!B22)
When Row 23 is inserted you would like the formula in Row 23 to be:
Code: 
=IF(Master!B23="","",Master!B23)

If the above is the case try using the below code. I made some small changes and this seemed to work for me:
Code: 
[COLOR=#0000ff]Sub[/COLOR] Test()

   [COLOR=#0000ff] Dim[/COLOR] RowNum [COLOR=#0000ff]As Integer[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] RowColor  [COLOR=#0000ff]As Integer[/COLOR]
    
    RowNum = Application.InputBox(Prompt:="Enter Row number for new item", Title:="Format Tables", Type:=1)
  [COLOR=#0000ff]  If[/COLOR] RowNum = 0 [COLOR=#0000ff]Then Exit Sub[/COLOR]
    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 <> "Instructions" [COLOR=#0000ff]Then[/COLOR]
               wksht.Activate
                With wksht
                   .Rows(RowNum).Select
                   Selection.EntireRow.Insert
                
                   [COLOR=#0000ff]  For[/COLOR] i = 1 [COLOR=#0000ff]To[/COLOR] 12 [COLOR=#0000ff]Step[/COLOR] 1
                        [COLOR=#0000ff] If [/COLOR].Cells(RowNum - 1, i).HasFormula [COLOR=#0000ff]Then[/COLOR]
                             .Cells(RowNum - 1, i).Copy Destination:=.Cells(RowNum, i)
[COLOR=#0000ff]                         End If[/COLOR]
                     [COLOR=#0000ff]Next[/COLOR] i
                     
                  [COLOR=#0000ff]   If [/COLOR]RowColor = 1 [COLOR=#0000ff]Then[/COLOR]
                             .Range("C" & RowNum & ":I" & RowNum).Select
                             Selection.Interior.Color = RGB(219, 229, 241)[COLOR=#008000] 'Colors rows blue[/COLOR]
                             Selection.Borders(xlEdgeRight).Color = 0
                             Selection.Borders(xlEdgeLeft).Color = 0
                             Selection.Borders(xlEdgeTop).Color = 0
                             Selection.Borders(xlEdgeBottom).Color = 0
                             Selection.Borders(xlInsideHorizontal).Color = 0
                             Selection.Borders(xlInsideVertical).Color = 0
[COLOR=#0000ff]                     Else[/COLOR]
                             .Range("C" & RowNum & ":I" & RowNum).Select
                             Selection.Interior.ColorIndex = 2 [COLOR=#008000] 'Colors rows white[/COLOR]
                             Selection.Borders(xlEdgeRight).Color = 0
                             Selection.Borders(xlEdgeLeft).Color = 0
                             Selection.Borders(xlEdgeTop).Color = 0
                             Selection.Borders(xlEdgeBottom).Color = 0
                             Selection.Borders(xlInsideHorizontal).Color = 0
                             Selection.Borders(xlInsideVertical).Color = 0
[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]
 
Upvote 0
Sort of. Columns A, B, J, K and L aren't part of the table. A, B and K have formula's that get their information from the Master sheet, where as J and L just have equations. Right now the formula's are copying correctly except that the formula is a row higher than it should be.

So column A row 22 has a formula of
Code: 
=IF(Master!A22="","",Master!A22)
, when it's copied with the macro it reads
Code: 
=IF(Master!A24="","",Master!A24)
instead of
Code: 
=IF(Master!A23="","",Master!A23)
. Does that make sense?

Maybe I have a problem with the workbook from all the changes and something latent is changing it?
 
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
146
spenserallen7
S
C
  • Question
Delete Cell Range, or Add Range and Copy Formulas etc. - Excel VBA
Replies
0
Views
142
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
107
DaveCutting
D
W
  • Solved
Row Transfer Macro
Replies
9
Views
149
mumps
mumps
E
  • Solved
Add row to table copying formatting
Replies
3
Views
307
skillilea
S

Forum statistics

Threads
1,216,102
Messages
6,128,846
Members
449,471
Latest member
lachbee

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