How do I insert a row in to all worksheets in Excel 2003?

Question

I'm working in Excel 2003 with the first tab as a master tab with the main details in and then each subsequent tab as a month in the year where I will input data that links to the master tab. Throughout the year I'll need to add new rows, is there any way I can add a new row in the master tab and it will automatically add the same row in the same place to the 12 monthly tabs. At the moment I'm having to go through each tab and add the row manually which is time consuming, is there an easier way to do this?

Answer 1

Here is a macro that will do as you ask. It assumes that your 'master' sheet is named "Master". After setting the following codes up in the VBE, the process will be totally automated.



All you have to do is double click a cell in the Master sheet and a row will be inserted in every sheet.



Copy the following code to the clipboard:



Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Insert_Row

End Sub



Select the 'Master' worksheet and right click the sheet tab.



Select 'View Code'.



Paste the code into the sheet module editing area to the right.



In the menus at the top of the VBE, select INSERT > MODULE.



Copy and paste the following code into the newly created module:



Sub Insert_Row()

Dim ws As Worksheet, actRow

If ActiveSheet.Name <> "Master" Then

Exit Sub

End If

actRow = ActiveCell.Row

For i = 1 To Worksheets.Count

Sheets(i).Activate

Rows(actRow & ":" & actRow).Select

Selection.Insert Shift:=xlDown

Range("A1").Select

Next

Sheets("Master").Activate

End Sub



Close the VBE and return to the worksheet.



To insert a row in the same position in all worksheets in the workbook, double click ANY cell in the row you wish to insert a row BELOW.



A new row will be inserted in all worksheets, including the Master. The macro will only function if called when the Master sheet is the active sheet.

Answer 2

Before you do any change to your workbook, make a backup (save it with another name)



You need to work with all the worksheet as a group, so follow these steps:



STEP 1A: CREATE A GROUP (adjacent worksheet)

• Click the tab of a worksheet. This worksheet will be your master worksheet of the group, in your case click on your master tab.

• Hold down the SHIFT key and click on the tab for the last worksheet that will be part of the group.

All the worksheets between the two selected worksheets (including them) should now be highlighted/selected. You have created a group.



STEP 1B: CREATE A GROUP (Non-adjacent worksheet)

• Click the tab of a worksheet. This worksheet will be your master worksheet of the group, in your case click on your master tab.

• Holding the CTRL key click on the tab of each worksheets you want to be part of the group.

All the worksheets selected should now be highlighted/selected. You have created a group.



STEP 2: MAKE THE CHANGES

Work with the master worksheet, making any change needed. Any change made in the master worksheet will be done in the same place on the rest of the worksheet.

The changes include deleting/adding rows/columns, working with formulas or data.



STEP 3: UNGROUP THE WORKSHEETS

When done, click on the tab of a worksheet other than the first in the group or master worksheet, to free the worksheets from the group.



And thats all.

TIP: This word for excel 2003/2007

Answer 3

Dear sir,



Steps for inserting new row in Worksheets in Excel 2003.



Step 1. Click on right mouse button.

Step 2. Click on insert submenu.

Step 3. Click on entire row radio button

Step 4. Click on ok button



A row will be inserted.





http://www.e-profitbooster.com

Answer 4

You could try to record this as a Macro (Tools - Macro - Record new Macro), go through the actions you want to do, stop recording and then figure out how to adda button to the screen (use the Control toolbar I think) and assign the macro to it - so when you press the button the macro runs, or go Tools - Macro - Macros and run the macro from there (or Alt +F8)



Hope that gives you an idea anyway

Answer 5

here is how I did it: a million. Open seen straight forward Editor (Alt+F11) and choose Insert>Module 2. Paste here code in Module1 that grew to become into merely created function UserNameWindows() As String UserNameWindows = Environ("USERNAME") end function function InsertHistoryRow() Dim uname As String uname = UserNameWindows() utility.ScreenUpdating = fake Sheets(3).pick Sheet3.Rows("a million").pick Sheet3.Rows("a million").Insert Shift:=xlDown Sheet3.selection("A1").fee = uname Sheet3.selection("B1").fee = Date Sheet3.selection("C1").fee = Time Sheets(a million).pick utility.ScreenUpdating = real end function 3. upload one line on your BeforeClose BeforeSave journey handlers InsertHistoryRow Like this: inner maximum Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If ThisWorkbook.stored = real Then Sheet1.selection("B1").fee = Date Sheet1.selection("C1").fee = Time InsertHistoryRow end If end Sub this could insert a clean row on the perfect of Sheet3 each and every time the workbook is stored. I did additionally substitute the line of code "If ThisWorkbook.stored = real Then" so as that it happens while the keep journey is fired. ......................................... in case you needed a header row on sheet3 for the historic previous you merely could desire to alter the rows interior the InsertRowHistory function. function InsertHistoryRow() Dim uname As String uname = UserNameWindows() utility.ScreenUpdating = fake Sheets(3).pick Sheet3.Rows("2").pick Sheet3.Rows("2").Insert Shift:=xlDown Sheet3.selection("A2").fee = uname Sheet3.selection("B2").fee = Date Sheet3.selection("C2").fee = Time utility.ScreenUpdating = real end function