travis.m.roberts
Board Regular
- Joined
- Jul 7, 2009
- Messages
- 62
I found this code and can't seem to get it to work for me. When a new sheet is inserted this code inserts/copies a row of formulas and stuff and then inserts a new row with the same formulas but with the new sheet name's applied. What I can't figure out is how to control what rows it starts at or ends at. I have a spreadsheet that, every month we have to add a new tab too and update the summary tab. This macro will be miracle if I can get it to work. The problem is that my spreadsheet is set on the first column and I have totals at the bottom. Is there a way to get this macro to insert rows NOT as the last row?
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim NewRowCell As Range
Set NewRowCell = Sheets("Main Sheet").Range("a65536").End(xlUp).Offset(1, 0)
NewRowCell.Offset(-1, 0).EntireRow.Copy Destination:=NewRowCell
NewRowCell.EntireRow.Replace What:=NewRowCell.Offset(-1, 0).Value, Replacement:=Sh.Name, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
NewRowCell.FormulaR1C1 = "=MID(CELL(""filename""," & Sh.Name & _
"!RC), FIND(""]"",CELL(""filename""," & Sh.Name & "!RC))+1,31)"
End Sub
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim NewRowCell As Range
Set NewRowCell = Sheets("Main Sheet").Range("a65536").End(xlUp).Offset(1, 0)
NewRowCell.Offset(-1, 0).EntireRow.Copy Destination:=NewRowCell
NewRowCell.EntireRow.Replace What:=NewRowCell.Offset(-1, 0).Value, Replacement:=Sh.Name, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
NewRowCell.FormulaR1C1 = "=MID(CELL(""filename""," & Sh.Name & _
"!RC), FIND(""]"",CELL(""filename""," & Sh.Name & "!RC))+1,31)"
End Sub