Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting rows macro
Hi,
I have this code, as below, coming from David McRitchie´s webpage. It works great, (Thanks, David), but I need help to extend it, so that it works for my needs. Now, I have a workbook with several simular sheets. It´s one sheet for every month of the year and some other sheets. In each "monthsheet" I have formulas that refers to one "main sheet" that contains all the information to be . I wonder if you can help me to create a macro that always starts in the "main sheet" and then inserts the same rows in all the other sheets at the same time, and also change the formulas from (i.e =c6 to =c7), and not as the formula is now, as it just duplicates. (i.e=c6 to =c6). All the sheets are protected with password, therefore I´ts also necessary to unprotect, and then protect the sheets in that same code. I hope you understand what I´m trying to reach for. Code: Sub InsertRowsAndFillFormulas(Optional vRows As Long) ' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm ' Insert Rows -- 1997/09/24 Mark Hill 'Dim vRows As Integer ' row selection based on active cell -- rev. 2000-09-02 David McRitchie ActiveCell.EntireRow.Select 'So you do not have to preselect entire row If vRows < 1 Then vRows = Application.InputBox(prompt:= _ "How many rows do you want to add?", Title:="Add Rows", _ Default:=1, Type:=1) '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. 20001-01-17 Gary L. Brown, programming, Grouped sheets Dim sht As Worksheet, shts() As String, i As Integer ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _ Windows(1).SelectedSheets.Count) i = 0 For Each sht In _ Application.ActiveWorkbook.Windows(1).SelectedShee ts Sheets(sht.Name).Select i = i + 1 shts(i) = sht.Name 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 End Sub //Regards, Thomas |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting rows macro
Hi Jonsson
Provided that you want to insert a row at the same place on ALL you worksheets, you could use the following code. 'Select multiple worksheets. Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select Select and insert the row that you wish Sheets("Sheet1").Activate Rows("3:3").Select Selection.Insert Shift:=xlDown One row has been inserted into each of the worksheets. Hope this help -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting rows macro
All the sheets are protected with password, therefore I´ts also necessary
to unprotect, and then protect the sheets in that same code. Assuming they were all unprotected at the time. -- Don Guillett SalesAid Software "liddlem " wrote in message ... Hi Jonsson Provided that you want to insert a row at the same place on ALL your worksheets, you could use the following code. 'Select multiple worksheets. Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select Select and insert the row that you wish Sheets("Sheet1").Activate Rows("3:3").Select Selection.Insert Shift:=xlDown One row has been inserted into each of the worksheets. Hope this helps --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro auto inserting rows and formulas | Excel Discussion (Misc queries) | |||
Macro Help- Inserting Blank Rows | Excel Discussion (Misc queries) | |||
Inserting Blank Rows Macro? | Excel Worksheet Functions | |||
Inserting Rows Through a Macro | Excel Programming | |||
INSERTING ROWS WITH A MACRO | Excel Programming |