Inserting a Batch Changes the Formatting of the Cells Below?

[Strider89]

Now, I've never been accused of being the smartest guy in the room, so I apologize if this is a simple and easy solution, but here goes.

Currently, we have a template that allows users to operate within a batch of specified sheets, with a pre-generated number of line items that they can add or detract from. Because of how specialized the information is, the new line items have to be pulled from a separate sheet and inserted as a new row. The problem is, at the very bottom of each sheet is a Total row, which is 25.50 units tall. When my users insert a new line or batch, the row height stays absolute to that row, rather than being relative. How can I fix this? New line and new batch codes below.

Sub InsertBatch()'
' InsertBatch Macro
'
' Keyboard Shortcut: Ctrl+Shift+N


Application.ScreenUpdating = False


    Dim rngToCopy As Range
    Set rngToCopy = ThisWorkbook.Worksheets("The Hidden Works").Range("A8:U19")


    rngToCopy.Copy
    Selection.EntireRow.Range("A1").Insert Shift:=xlDown
    Application.CutCopyMode = False


  
    Sheets("The Hidden Works").Visible = -1
 
    
Application.ScreenUpdating = True


End Sub

Sub InsertRow()

Application.ScreenUpdating = False


    Sheets("The Hidden Works").Visible = -1
    Sheets("The Hidden Works").Range("A21:U21").Copy
    ActiveCell.Rows("1:1").EntireRow.Insert Shift:=xlDown
    Sheets("The Hidden Works").Visible = 2
    


Application.ScreenUpdating = True


End Sub

 

[Fluff]

How about

Sub InsertBatch() '
' InsertBatch Macro
'
' Keyboard Shortcut: Ctrl+Shift+N


Application.ScreenUpdating = False
  [COLOR=#0000ff] Dim Lr As Long[/COLOR]
   Dim rngToCopy As Range
   [COLOR=#0000ff]Lr = Range("A" & Rows.Count).End(xlUp).Row[/COLOR]
   Set rngToCopy = ThisWorkbook.Worksheets("The Hidden Works").Range("A8:U19")
   
   rngToCopy.Copy
   Selection.EntireRow.Range("A1").Insert shift:=xlDown
   Application.CutCopyMode = False
  [COLOR=#0000ff] Rows(Lr).AutoFit
   Rows(Lr + 12).RowHeight = 25.5[/COLOR]
   
   
   Sheets("The Hidden Works").Visible = -1
   
   
Application.ScreenUpdating = True


End Sub

And something similar for the other macro

 

[Strider89]

I think we're on the right track with this one, Mr. Fluff. However, the macro now constantly sets row 12 to 25.5, regardless of where I decide to insert the new batch, and always sets the last row to AutoFit. What if, after the copy paste, I get all the rows within a range autofit, and then the bottom row with data to be set to 25.5? Is that possible?

 

[Fluff]

Do you have data all the way down col A

 

[Strider89]

No; the bottom row has data in C, H, J, N, P, T, and U.

 

[Fluff]

In that case change this line

 Lr = Range("[COLOR=#ff0000]C[/COLOR]" & Rows.Count).End(xlUp).Row

 

[Strider89]

In that case change this line

 Lr = Range("[COLOR=#ff0000]C[/COLOR]" & Rows.Count).End(xlUp).Row

Thank you so much!

 

[Fluff]

Glad to help & thanks for the feedback