VBA to Insert Blank Rows

[happyhungarian]

Hi,

I need help creating a VBA that will insert a blank row and insert that row onto multiple sheets. The real trick is that I need that Macro to be dynamic. For example: I have multiple regions with multiple offices under each region. I need to be able to press the macro run button, have a message pop up (preferably with a drop down menu) asking which region i would like to add an office to. When I select which region, it will find the section for the region and add a line for a new office on the active sheet as well as three other sheets. I know this is a tall order but if someone could tell me if it's even possible that would be great.

Jesse

 

[Trevor G]

You could look at using a userForm from the VBA screen and then you can add a drop down (Combo Box) which can list all the Regions and then look at using some code to add the rows.

Do you know about userForms in Excel?

 

[happyhungarian]

To be honest I am very novice with VBA... Here is a code that I started writing that inserts a row onto multiple sheets but it doesn't have the dynamic functionality that I am looking for:

Sub Insert_Rows()

Dim sh As Worksheet


For Each sh In Sheets
If sh.Name = "Utilities2" Or _
sh.Name = "Rent2" Or _
sh.Name = "Repair&Maint2" Then
With sh
.Cells(ActiveCell.Row, 4).EntireRow.Insert
.Range(.Cells(ActiveCell.Row, 1), .Cells(ActiveCell.Row, 4)).FillDown
End With
End If
Next sh
End Sub