Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2004
    Location
    Itasca, Illinois, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to edit multiple sheets (Excel 2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Macro to edit multiple sheets (Excel 2003)

    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.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to edit multiple sheets (Excel 2003)

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

    <pre>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
    </pre>

    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Jun 2004
    Location
    Itasca, Illinois, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to edit multiple sheets (Excel 2003)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •