如何在工作簿中的一些但不是所有工作表上运行宏？ [英] How to run a macro on some but not all sheets in a workbook?

问题描述

我有一个工作簿，其中包含S& P 500中每个行业组的工作表，并在下列宏中写下宏，以便在按下第一个工作表上的命令按钮时更新其上的所有库存信息。宏可以完美地工作，但是当我去添加我不想用这个宏更新的其他工作表时，它会停止工作。我尝试使用下面的If Not语句，但似乎没有起作用。

I have a workbook that contains worksheets for each industry group in the S&P 500 and wrote the macro below to update all the stock information on them when I press a command button on the first worksheet. The macro works perfectly, but when I go to add additional sheets that I do not want to update with this macro it stops working. I tried using the "If Not" statements below, but it did not seem to work.

Sub Get_Stock_Quotes_from_Yahoo_Finance_API()

'Run the API for every sheet in the workbook
Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Worksheets

    'Look to see what the sheet is named and run the macro if it is not what is below
    If Not Sht.Name = "Cover" _
    And Not Sht.Name = "Select Industry" Then

    Sht.Activate

        ' Dim varibales and set range
        Dim head As Range
        Set head = Worksheet.Range("A2")

        'dim variables
        Dim I As Integer
        Dim Symbols As String: Symbols = ""
        Dim SpecialTags As String: SpecialTags = ""
        Dim Yahoo_Finance_URL As String: Yahoo_Finance_URL = "http://finance.yahoo.com/d/quotes.csv?s="
        Dim rng As Range
        Dim cell As Range

        ' Get the Stock Symbols
        Set rng = Range(head.Offset(1, 0), head.Offset(1, 0).End(xlDown))
        For Each cell In rng ' Starting from a cell below the head cell till the last filled cell
            Symbols = Symbols & cell.Value & "+"
        Next cell
        Symbols = Left(Symbols, Len(Symbols) - 1) ' Remove the last '+'

        ' Get the Special Tags
        Set rng = Range(head.Offset(0, 1), head.Offset(0, 1).End(xlToRight))
        For Each cell In rng ' Starting from a cell to the right of the head cell till the last filled cell
            SpecialTags = SpecialTags & cell.Value
        Next

        ' Put the desciption/name of each tag in the cell above it
        Dim SpecialTagsArr() As String: Dim TagNamesArr() As String
        Call Get_Special_Tags(SpecialTagsArr, TagNamesArr)
        For Each cell In rng
            cell.Offset(-1, 0).Value = FindTagName(cell.Value, SpecialTagsArr, TagNamesArr)
        Next


        Yahoo_Finance_URL = Yahoo_Finance_URL & Symbols & "&f=" & SpecialTags

        Call Print_CSV(Yahoo_Finance_URL, head)

Next Sht
'At the end of the program say it has all been updated
MsgBox ("All Data Updated")
End Sub

推荐答案

更改

 If Not Sht.Name = "Cover" _
    And Not Sht.Name = "Select Industry" Then

至

If Sht.Name <> "Cover" And Sht.Name <> "Select Industry" Then 

不要忘记你的 End If before Next Sht

Don't forget your End If before Next Sht

这篇关于如何在工作簿中的一些但不是所有工作表上运行宏？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！