Insert row into multiple worksheets simultaneously

[paulhorn]

Can anyone advise if it is possible using VBA to insert a row at the same point into several worksheets at the same time (to include the formatting and formula info, but excluding values)?

I run a spreadsheet coordinating the rota and activities of 50 volunteers across the year. The workbook is split into monthly worksheets, with some summary sheets to highlight aspects of some activities, and to produce duty rosters.

It works well for most things , but the problem start when I have to add/remove volunteers (names are stored alphabetically in column A).

Currently, I resist changes until a new year starts, and add new volunteers on at the bottom, so destroying the aplhabetical layout.

Any advice gratefully received

 

[Jubjab]

Can't you just add the new names last, and then resort the list to restore the alphabetical order?

 

[Peter_SSs]

paulhorn

A starting point for your macro would be to turn the macro recorder on, do the task (noting that you can select a range of worksheets by selecting one and holding Shift to select a continuous group of sheets or holding Ctrl to select individual sheets) and then turn the recorder off.

You would most likely have to modify the code, but at least you should have a starting point and then be able to ask specific questions about your code if needed.

 

[paulhorn]

Hi Jubjab,
The problem here is that data has already been entered against a particulr person in a given row. I suppose that it would work if you could simultaneously select the same data ranges across several sheets. I'll try this with Peters suggestion below

THanks peter,
I'll go down this route (just thought it might be difficult to do across multiple sheets with the recorder, and given my limited VBA knowledge!)

 

[Peter_SSs]

Hi Jubjab,
The problem here is that data has already been entered against a particulr person in a given row. I suppose that it would work if you could simultaneously select the same data ranges across several sheets. I'll try this with Peters suggestion below

THanks peter,
I'll go down this route (just thought it might be difficult to do across multiple sheets with the recorder, and given my limited VBA knowledge!)

If you select multiple sheets (with Shift and/or Ctrl) as I described then whatever you do to the visible (active) sheet, will happen on all of the sheets (eg formatting a range, entering a value in a particular cell, inserting a row above row 10 etc). Although this can be very useful, and I am sure will be to you in this project, it can also be very annoying. If you forget to "ungroup" the sheets at the end of the process you can suddenly find changes made to sheets that you didn't intend.

Once you have done whatever you want to the group of sheets you can ungroup them by selecting a sheet that is not in the group or by right-clicking the tab of one of the grouped sheets and choosing "Ungroup Sheets". Also, keep an eye on the title bar as it will tell you when you have a group of sheets selected.

 

[paulhorn]

OK...I have created the macros to fulfil the following;
Insert a row into multiple worksheets,
Copy cell formulas from the PRECEDING row for each of those worksheets
Clear the contents of a SUBSET of those worksheets over a REDUCED range.

My question is....How do I alter this code so that I can perform the sequence of events on any row that I select (as opposed to the fixed row shown in the code?





Sub Insert1()
'
'
Sheets(Array("VRs", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", _
"Nov", "Dec", "Rosters")).Select
Sheets("VRs").Activate
Range("A18:ET18").Select
Selection.EntireRow.Insert
Sheets("VRs").Select
Range("A18").Select
End Sub


Sub Insert2()
'

Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", _
"Dec", "Rosters")).Select
Sheets("Jan").Activate
Range("A17:ET17").Select
Selection.Copy
Range("A18:ET18").Select
Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A18").Select
Sheets("VRs").Select
End Sub


Sub Insert3()
'
'
Range("D17:Y17").Select
Selection.Copy
Range("D18:Y18").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

Sub Insert4()
'

'
Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", _
"Dec")).Select
Sheets("Jan").Activate
Range("D18:AO18").Select
Selection.ClearContents
End Sub

 

[paulhorn]

Just had a spark of light at the end of the tunnel - could I use 'InputBox' in some way to solve this (all that is required is the row number, since the ranges remain the same)?
Any advice appreciated