Macro to edit multiple sheets (Excel 2003)

Topic

New Reply

I have the following code that I am using to save a few keystrokes. I want the rows inserted on both tabs (“Link” and “Data”) in the same location. My macro will only insert the rows in the active sheet:

Sheets(Array(“Link”, “Data”)).Select
Sheets(“Link”).Activate
Selection.EntireRow.Insert
ActiveCell.Offset(23, 0).Range(“A1”).Select
Selection.EntireRow.Insert
ActiveCell.Offset(26, 0).Range(“A1”).Select
Selection.EntireRow.Insert

I can do this manually by selecting the two sheets but cannot figure out why the macro will not do this with the Array Select.

Any guidance will be greatly appreciated.

Bill

Reply | Quote

Replies

ActiveCell refers to the active worksheet only, it can’t refer to multiple sheets. Try this:

Dim lngRow As Long
lngRow = ActiveCell.Row + 23
Sheets(Array(“Link”, “Data”).Select
Rows(lngRow & “:” & lngRow).Insert

This code determines the row to be inserted before selecting the two sheets.

Reply | Quote

Working with multiple sheets only seems to work from the user interface, not from VBA. Try this:

Dim oSheet As Object
    For Each oSheet In Worksheets(Array("Link", "Data"))
        oSheet.Range(Selection.Address).EntireRow.Insert
        oSheet.Range(Selection.Address).Offset(23, 0).Range("A1").EntireRow.Insert
        oSheet.Range(Selection.Address).Offset(49, 0).Range("A1").EntireRow.Insert
    Next oSheet

Reply | Quote

Thank you Hans and Legare.

I tried both of your responses and was able to work Legare’s code in a little easier. I will save both responses for future reference.

Thank you both again for your valuable assistance.

Reply | Quote