Adding new lines in multiple spreadsheets

[gillybo]

Hello,

I am trying to do the following and am having issues identifying the appropriate macro to execute:

Using MS Excel 2013
Macro should insert a line in "Monthly Time" Tab and a new line should automatically enter in the "Monthly Dollars" Tab in the exact same row as entered on "Monthly Time". The new lines will be added at various points in the Sheet, so we need the ability to specify where the new row(s) will be added. When the new line is added, it needs to bring all formatting & formulas from the above cell with it


I'm new to VBA and have been told the code I was using isn't the best place to start (vbaexpress.com/forum/showthread.php?1528-Inserting-Rows-and-keeping-the-same-formats-and-continuing-the-formulas#post369564)

So any assistance in writing the appropriate code would be greatly appreciated!

[austenr]

could you please post your code and a sanitized version of your workbook

[gillybo]

Attached is a scaled version of the document I'm using.

Regarding the code, below is the link of where I was starting (
vbaexpress.com/forum/showthread.php?1528-Inserting-Rows-and-keeping-the-same-formats-and-continuing-the-formulas#post369564)
). I've also pasted the working version of what I'm trying to do, minus copying the format (though I assume it's very incorrect)


Sub NewTIMERow()

Dim cs As String
cs = ActiveSheet.Name
Dim y As Integer
y = Application.InputBox("Enter the row number you wish to add", _
Type:=1)
If MsgBox("Are you sure you wish to insert at row " & y & " for Monthly Time & Dollar Forecasts?", _
vbYesNo, "Insert row on ALL Sheets") = vbNo Then Exit Sub
Application.ScreenUpdating = False

Dim r As Range
Dim ws As Worksheet
Dim strWS1 As String, strWS2 As String, strWS3 As String, strWS4 As String, strWS5 As String, strWS6 As String, strWS7 As String, strWS8 As String

strWS1 = "A"
strWS2 = "B"
strWS3 = "Monthly Time"
strWS4 = "Monthly Dollars"
strWS5 = "E"
strWS6 = "F"
strWS7 = "G"
strWS8 = "H"

If ActiveSheet.Name = strWS1 Or ActiveSheet.Name = strWS2 Or ActiveSheet.Name = strWS5 Or ActiveSheet.Name = strWS6 Or ActiveSheet.Name = strWS7 Or ActiveSheet.Name = strWS8 Then
Exit Sub

Else
If ActiveSheet.Name = strWS3 Then
Worksheet.Activate
Set r = ActiveSheet.Range("A" & y)
Range("A" & y).EntireRow.Insert
End If
End Sub

[austenr]

Dont know if you still are working on this or not but I cleaned it up a lot and took out the un necessary code.

Seems to work as it inserts a row but not usre what you want in the inserted row?


Option Explicit

Sub NewTIMERow()

Dim cs As String
cs = ActiveSheet.Name
Dim y As Integer
y = Application.InputBox("Enter the row number you wish to add", _
Type:=1)
If MsgBox("Are you sure you wish to insert at row " & y & " for Monthly Time & Dollar Forecasts?", _
vbYesNo, "Insert row on ALL Sheets") = vbNo Then Exit Sub
Application.ScreenUpdating = False

Dim r As Range
Dim ws As Worksheet
Dim strWS3, strWS4 As String

strWS3 = "Monthly Time"
strWS4 = "Monthly Dollars"


If ActiveSheet.Name = strWS3 Then
Set r = ActiveSheet.Range("A" & y)
Range("A" & y).EntireRow.Insert

Else

End If
End Sub

[gillybo]

Thank you Austenr!

I needed it by yesterday, but after a few more hours of trying I ended up with something very close. This updates two tabs at once with the same data and then copies the format / formulas from the cells above.

Sub NewTIMERow()

Dim cs As String
cs = ActiveSheet.Name
Dim y As Integer
y = Application.InputBox("Enter the row number you wish to add", _
Type:=1)
If MsgBox("Are you sure you wish to insert at row " & y & " for Monthly Time & Dollar Forecasts?", _
vbYesNo, "Insert row on ALL Sheets") = vbNo Then Exit Sub
Application.ScreenUpdating = False

Dim r As Range
Dim ws As Worksheet
Dim strWS3, strWS4 As String

strWS3 = "Monthly Time"
strWS4 = "Monthly Dollars"

Worksheets(strWS3).Activate Set r = ActiveSheet.Range("A" & y)
With Range("A" & y)
.EntireRow.Insert
.Offset(-2, 0).EntireRow.Copy
.Offset(-1, 0).PasteSpecial xlPasteAll
End With

Worksheets(strWS4).Activate
Set r = ActiveSheet.Range("A" & y)
With Range("A" & y)
.EntireRow.Insert
.Offset(-2, 0).EntireRow.Copy
.Offset(-1, 0).PasteSpecial xlPasteAll

End Sub

[austenr]

just some things i noticed as you go forward. in the original code you had a bunch of unnecessary variables defined and some redundant code. its all good though as everybody starts out a little rough and learns as they go. this is a great resource and awesome place to learn and get help. Enjoy!!