Diddi & Michael M (& Fluff!),
I had a couple of different threads going here on Mr Excel attempting to try different workarounds, and this question about launching a script when opening the tab is now a non-working workaround. You all gave me great advise of how to do it, but the very reason that I needed it to begin with is now null & void (because the macro that I created which I needed the script to warn about... is not working as I planned. BUT... I've finally had the "come to Jesus" talk with myself about not being so resistant to wading into the deep & unfamiliar waters of VB, and so I'm over it now. In fact, it seems like perhaps just adding some code could turn a weeklong hand-banging task into a simple copy/paste action which resolves everything. So I'm in! (if someone can help me)
To put it as simple as I can, here is the current situation. My thought was that when a new store is grand-opened (which will not necessarily keep running in the same numeric order which follows the store 101, store 102, store 103 pattern), the sheet user would add a new row to the bottom of the Data tab, which would automatically create the same additional new row at the bottom of the Yest/MTD/YTD tabs of the MOR tab). The user would then enter the new store ID info into the new row of the data tab, which would of course appear then in each of the 3 corresponding MOR tab ranges. But going the 'adding code' route seems like it will just make my life tremendously easier, and eliminate the need for all of these failsafe measures & confusing workarounds I've been attempting... which seem to create as many problems as they solve.
This is my code for the macro to create a total of 4 new rows in 2 separate tabs. The code was recorded in, and definitely has some noise in it. While strangely it worked perfectly at times, about 65% of the time it was adding and taking away rows from the wrong places. How can I just make this happen?
Here is the code, as well as 2 current screenshots of the 2 relevant sheets in the workbook.
Sub AddNewRow()
'
' AddNewRow Macro
' This macro first adds a new row to the bottom of Location Master List below (to allow user to enter a new store), then the macro inserts a corresponding new row at on the MOR sheet at the bottom of each (Yest/MTD/YTD) table.
' The MOR tab portion was accomplished by selecting cell D5 to start, then CTRL-click-DOWN ARROW to skip to next sections, and CTRL-SHIFT-RIGHT ARROW to highlight the rows and copy, then arrowing down to the newly created cell and pasting to get all the formatting and everything. The macro will not work if I try to select the whole row, so I had to select the entire length of the table using this method.
'
Range("B3").Select
Selection.End(xlDown).Select
Range("B9:C9").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B10:C10").Select
Selection.Copy
Range("B9").Select
ActiveSheet.Paste
Range("B10:C10").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("C10").Select
Sheets("MOR").Select
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Sheets("MOR").Select
Range("D5").Select
Selection.End(xlDown).Select
Range("D10").Select
Selection.Copy
Range("D11").Select
ActiveSheet.Paste
Range("D11:S11").Select
Application.CutCopyMode = False
Selection.Copy
Range("D12:S12").Select
Selection.Insert Shift:=xlDown
Range("D11").Select
Application.CutCopyMode = False
Selection.Copy
Range("D12").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range("D21").Select
Application.CutCopyMode = False
Selection.Copy
Range("D22").Select
ActiveSheet.Paste
Range("D22:S22").Select
Application.CutCopyMode = False
Selection.Copy
Range("D23:S23").Select
Selection.Insert Shift:=xlDown
Range("D22").Select
Application.CutCopyMode = False
Selection.Copy
Range("D23").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range("D32").Select
Application.CutCopyMode = False
Selection.Copy
Range("D33").Select
ActiveSheet.Paste
Range("D33:S33").Select
Application.CutCopyMode = False
Selection.Copy
Range("D34:S34").Select
Selection.Insert Shift:=xlDown
Range("D33").Select
Application.CutCopyMode = False
Selection.Copy
Range("D34").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.LargeScroll Down:=-1
Range("E6").Select
Sheets("Data").Select
Range("B10").Select
ActiveCell.FormulaR1C1 = ""
Range("B10").Select
ActiveCell.FormulaR1C1 = "107"
Range("B11").Select
Sheets("MOR").Select
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Sheets("Data").Select
Range("B3").Select
Selection.End(xlDown).Select
Selection.ClearContents
End Sub