Insert new line on three worksheets

[glenn0004]

I'm afraid that my VBA skills are minimum but I have been asked to create a sales order workbook that contains 4 worksheets, of which three are to contain order-lines. For each item sold, a sales person will enter a new row on sheet 1 and the same data is copied to sheets 2 and 3 but with additional data.

I'm using the below vba (taken from an existing workbook that we are using) to aid the creation of rows on sheet 1 (copies the formula format from a selected line and inserts a new a defined number of rows below) what I need is the vba to create the same number of new rows on sheets 2 and 3 as a new line on sheet 1 is created but using the format of existing rows on each sheet. The aim is to save a sales person from having to enter new rows on each sheet individually.

Is this possible via vba ?

and as always thank you in advance!

Sub InsertRowsAndFillFormulas_caller()
  '-- this macro shows on Tools, Macro..., Macros (Alt+F8) dialog
  Call InsertRowsAndFillFormulas
End Sub
 
Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
 ActiveSheet.Unprotect Password:="bunny"


   Dim x As Long
   ActiveCell.EntireRow.Select  'So you do not have to preselect entire row
   If vRows = 0 Then
    vRows = Application.InputBox(prompt:= _
      "How many rows do you want to add?", Title:="Add Rows", _
      Default:=1, Type:=1) 'Default for 1 row, type 1 is number
    If vRows = False Then Exit Sub
   End If


   'if you just want to add cells and not entire rows
   'then delete ".EntireRow" in the following line


   'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheets
   Dim sht As Worksheet, shts() As String, i As Long
   ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
       Windows(1).SelectedSheets.Count)
   i = 0
   For Each sht In _
       Application.ActiveWorkbook.Windows(1).SelectedSheets
    Sheets(sht.Name).Select
    i = i + 1
    shts(i) = sht.Name


    x = Sheets(sht.Name).UsedRange.Rows.Count 'lastcell fixup


    Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
     Resize(rowsize:=vRows).Insert Shift:=xlDown


    Selection.AutoFill Selection.Resize( _
     rowsize:=vRows + 1), xlFillDefault


    On Error Resume Next    'to handle no constants in range -- John McKee 2000/02/01
    ' to remove the non-formulas -- 1998/03/11 Bill Manville
    Selection.Offset(1).Resize(vRows).EntireRow. _
     SpecialCells(xlConstants).ClearContents
   Next sht
   Worksheets(shts).Select
       ActiveSheet.Protect Password:="bunny"
End Sub

 

[AFPathfinder]

Hi, glenn0004,

I'm pretty sure there is a way to accomplish what you are looking for, but I'd like some more information.

The user inserts a variable number or rows in sheet 1 and the same number or rows needs to be added to sheets 2 and 3? Should the inserted rows be in the same location on all three sheets? Will the inserted rows always start with a particular row number?

Is there a value in a cell directly below the inserted rows to be used as an anchor? This would allow you to count the rows inserted each time using syntax for keyboard shortcuts (.End(xlDown)).

 

[glenn0004]

Hi AFPathfinder

Many thanks for getting back to me:

The user inserts a variable number or rows in sheet 1 and the same number or rows needs to be added to sheets 2 and 3? Should the inserted rows be in the same location on all three sheets? - Yes my intention idea is that if the user inserts 3 rows in sheet one (Equipment), 3 rows will be added (copying the format of the selected line) to the 2nd and 3rd sheets (Summary and IOS) the 3 different table / data areas are in the same location (row) on each sheet.

Will the inserted rows always start with a particular row number? - not intentionally, each sheet contains 3 different table / data areas, so new rows could be added to any of the 3 table / data areas.

Is there a value in a cell directly below the inserted rows to be used as an anchor? This would allow you to count the rows inserted each time using syntax for keyboard shortcuts (.End(xlDown)). - There will be a blank row between each of the 3 different table / data areas.

Once again, many thanks for your help.

 

[AFPathfinder]

Will you be relying on a user to click a button to run the mirror, or a worksheet event?

Here is a quick pair of worksheet event macros that will mirror the inserted rows to the other two sheets, but there is a possibility of error.

Option Explicit
Private Sub Worksheet_Activate()
'Runs when Sheet1 is activated

    Dim finalRow As Integer
    
    'Locate the last row in the dataset and place the row number in cell K1
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("K1").Value = finalRow
    
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Runs when a change occurs on Sheet1

    Dim finalRow As Integer
    Dim j As Long, k As Long
    
    'Locate the last row in the dataset
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    'Compare the current last row and the previous last row (cell K1 value)
    If finalRow > Range("K1").Value Then
    
        'Assumes the user selected the rows in ascending order
        j = ActiveCell.Row
        k = ActiveCell.End(xlDown).Row
        Sheets("Sheet2").Range("A" & j).Resize(k - j).EntireRow.Insert
        Sheets("Sheet3").Range("A" & j).Resize(k - j).EntireRow.Insert
    End If
    
End Sub

1. Uses cell K1 to record the last row variable for comparison. Won't work properly if row 1 can be an inserted row.
2. If the user inserts specific cells in the first column, but not whole rows, whole rows will be inserted on the other two sheets.
3. If the user wishes to undo the inserted rows, the undo option is not available.
4. Deleted rows are not shared to the other two sheets.

Let me know what your thoughts are on this and we can make some adjustments.