0

I am creating a blank workbook with a command button that upon clicking, I want it to perform actions on EVERY open workbook currently open (as I will have other workbooks that aren't blank that I want it to carry out operations on).

I get a Subscript out of range error when I run:

Sub Button1_Click()
'
' Button1_Click Macro
' Fire Ext. Comments
'
'
Dim w As Workbook

' For every open workbook...
For Each w In Application.Workbooks

    ' Activate the current workbook
    w.Activate

    ' Find the comments column (K12 should be the "Comments" column)
    If Worksheets("FIRE EXT.").Range("K12").Value = "Comments" Then

        ' Then we loop through all cells in the specified range (anything below the header row)
        Dim rng As Range, cell As Range

        ' I'm using a range of 500 to look for values, so if a file has more than 500 rows, you'll have to look at it manually
        Set rng = Range("A1:A500")

        ' No loop to change all comments
        For Each cell In rng
.......................

...at the "If Worksheets("FIRE EXT.").Range("K12").Value = "Comments" Then" line. So I'm thinking it's starting with the blank workbook and not finding the worksheet named "FIRE EXT.", so what would be the best practise to first test if the activated workbook first has that sheet name, otherwise move on to the next workbook? Thanks!

UPDATE

This was what worked for me, but the other responses would have worked too. Thanks everyone!

Sub Button1_Click()
'
' Button1_Click Macro
' Fire Ext. Comments
'
'
Dim w As Workbook

' For every open workbook...
For Each w In Application.Workbooks

    ' Don't work on the current/blank workbook
    If w.FullName <> ThisWorkbook.FullName Then

    ' Find the comments column (K12 should be the "Comments" column)
    If w.Sheets("FIRE EXT.").Range("K12").Value = "Comments" Then

        ' Then we loop through all cells in the specified range (anything below the header row)
        Dim rng As Range, cell As Range

        ' I'm using a range of 500 to look for values, so if a file has more than 500 rows, you'll have to look at it manually
        Set rng = w.Worksheets("FIRE EXT.").Range("A13:A500")

        ' No loop to change all comments
        For Each cell In rng
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
wildcat89
  • 1,159
  • 16
  • 47
  • 1
    Possible duplicate of [Test or check if sheet exists](https://stackoverflow.com/questions/6688131/test-or-check-if-sheet-exists) – BigBen May 08 '19 at 20:06
  • 1
    Not your answer, but instead of `w.Activate`, try using `With w`, and use `.` notation to support qualifications. – Cyril May 08 '19 at 20:15
  • 1
    Related to @BigBen 's link, you can use a simplified approach such that `If IsError(Evaluate("'Fire EXT.'!A1")) Then` before performing actions on your workbook – Cyril May 08 '19 at 20:18

2 Answers2

2

You need to fully qualify all of your references. You can also put in a check to ensure it skips the blank workbook (see my comments in this updated code):

Sub Button1_Click()
'
' Button1_Click Macro
' Fire Ext. Comments
'
'
Dim w As Workbook

' For every open workbook...
For Each w In Application.Workbooks

    If w.FullName <> ThisWorkbook.FullName Then     '<-- TA: Add check to verify you're not working on the blank workbook

        'TA: I removed the .Activate line, that is never necessary.  Instead, fully qualify all your references

        ' Find the comments column (K12 should be the "Comments" column)
        If w.Worksheets("FIRE EXT.").Range("K12").Value = "Comments" Then   '<-- TA: Note that Worksheets is now qualified to w so that it is checking worksheets in the current w workbook

            ' Then we loop through all cells in the specified range (anything below the header row)
            Dim rng As Range, cell As Range

            ' I'm using a range of 500 to look for values, so if a file has more than 500 rows, you'll have to look at it manually
            Set rng = w.Worksheets("FIRE EXT.").Range("A1:A500")    '<-- TA: Note that Range is now qualified to w.Worksheets("FIRE EXT.") (if that isn't the correct sheet name, change this to the correct sheet name)

            ' Now loop to change all comments
            For Each cell In rng
.......................
tigeravatar
  • 26,199
  • 5
  • 30
  • 38
1

I wanted to type a comment, but it got pretty long.

  1. You will get an error if the Sheet Name "FIRE EXT." (with the period) does not exist on the workbook you are working with. You are looping ALL workbooks, if you have one that doesn't have that sheet, it will error out.
  2. It's better to stick with Sheets when using the Sheet Name, and Worksheets when using the Sheet Number. Sheets("SheetName") vs Worksheets(1)

  3. Avoid using Activate/Select by using the assigned workbook variable, in your code, that's "w"

    Sub Button1_Click()
Dim w As Workbook

' For every open workbook...
For Each w In Application.Workbooks

    ' Find the comments column (K12 should be the "Comments" column)
    If w.Sheets("FIRE EXT.").Range("K12").Value = "Comments" Then

        ' Then we loop through all cells in the specified range (anything below the header row)
        Dim rng As Range, cell As Range

        ' I'm using a range of 500 to look for values, so if a file has more than 500 rows, you'll have to look at it manually
        Set rng = w.Sheets("FIRE EXT.").Range("A1:A500")

        ' Now loop to change all comments
        For Each cell In rng
        ' Now here you dont use "w.Sheets("FIRE EXT.")" because it is already set on `rng'
        ' so you can just use `cell` like cell.value = "Blah"
.........
Ricardo A
  • 1,752
  • 1
  • 10
  • 16