Announcement

Collapse
No announcement yet.

Excel 2007 VBA - Multiple Macros

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Excel 2007 VBA - Multiple Macros

    Hey everyone, new to the site and this is my first post.

    I have searched numerous times to find an answer to the question "How to create a 'main' macro to control other macros within a workbook". I have my individual macros created. There's one macro for each sheet that searches online data and returns the information. I have one of these per sheet (a total of 20 sheets) since I couldn't find a way to have all 20 macros be on one sheet and still work. My trouble is that whenever I'm on my 'main' sheet and try to run the macro which applies to a 'secondary' sheet, I get an error. I have to select the sheet first, then run the macro and it works. Below is the macro on each sheet.

    Code:
    Sub Holding1()
        Dim DataSheet As Worksheet
        Dim EndDate As Date
        Dim StartDate As Date
        Dim Symbol As String
        Dim qurl As String
        Dim nQuery As Name
        
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Application.Calculation = xlCalculationManual
        
        Set DataSheet = Sheet2
      
            StartDate = DataSheet.Range("B3").Value
            EndDate = DataSheet.Range("B4").Value
            Symbol = DataSheet.Range("E10").Value
            Range("A32").CurrentRegion.ClearContents
            
            qurl = "http://ichart.yahoo.com/table.csv?s=" & Symbol
            qurl = qurl & "&a=" & Month(StartDate) - 1 & "&b=" & Day(StartDate) & _
                "&c=" & Year(StartDate) & "&d=" & Month(EndDate) - 1 & "&e=" & _
                Day(EndDate) & "&f=" & Year(EndDate) & "&g=" & Range("E3") & "&q=q&y=0&z=" & _
                Symbol & "&x=.csv"
            Range("A7") = qurl
                       
    QueryQuote:
                 With Sheet2.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("A32"))
                    .BackgroundQuery = True
                    .TablesOnlyFromHTML = False
                    .Refresh BackgroundQuery:=False
                    .SaveData = True
                End With
                
                DataSheet.Range("A32").CurrentRegion.TextToColumns Destination:=Range("A32"), DataType:=xlDelimited, _
                    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                    Semicolon:=False, Comma:=True, Space:=False, other:=False
                
                Range(Range("A32"), Range("A32").End(xlDown)).NumberFormat = "mm/dd/yy"
                Range(Range("B32"), Range("B32").End(xlDown)).NumberFormat = "0.00"
                Range(Range("C32"), Range("C32").End(xlDown)).NumberFormat = "0,000"
                Range(Range("D32"), Range("D32").End(xlDown)).NumberFormat = "0.00"
    End Sub
    Now, in VBA, this code is listed under "Module1". Is this is best place to put it, or do I put this code under "Sheet1", "Sheet2", etc.? Again, the ultimate goal is to create a 'main' macro that will run all 20 macros without having to first navigate to the sheet and then run. Thanks for any help.


    **Update**

    The error I get when running the 'main' macro from another sheet is Run-time error '1004': No data was selected to parse. When I debug it highlights the series of code use for text to columns, shown below. When I look at the sheet that is supposed to have the data populated, it pulls from the website, but just doesn't perform the text to column function and it moves all my other data over one column.

    Code:
    DataSheet.Range("A32").CurrentRegion.TextToColumns Destination:=Range("A32"), DataType:=xlDelimited, _
                    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                    Semicolon:=False, Comma:=True, Space:=False, other:=False
    Last edited by MPierce486; November 7th, 2012, 05:31. Reason: Update

  • #2
    Re: Excel 2007 VBA - Multiple Macros

    You can probably re-write this to apply to multiple pages, assuming they all have the same format. You just have to ensure you reference the sheet properly:

    Code:
    Sub Holding1() 
        Dim DataSheet As Worksheet 
        Dim EndDate As Date 
        Dim StartDate As Date 
        Dim Symbol As String 
        Dim qurl As String 
        Dim nQuery As Name 
         
        Application.ScreenUpdating = False 
        Application.DisplayAlerts = False 
        Application.Calculation = xlCalculationManual 
    
        for each datasheet in thisworkbook.worksheets
            if ucase(datasheet.name) <> "MAIN" then
    '    Set DataSheet = Sheet2 
         
        StartDate = DataSheet.Range("B3").Value 
        EndDate = DataSheet.Range("B4").Value 
        Symbol = DataSheet.Range("E10").Value 
        DataSheet.Range("A32").CurrentRegion.ClearContents 
    
        qurl = "http://ichart.yahoo.com/table.csv?s=" & Symbol 
        qurl = qurl & "&a=" & Month(StartDate) - 1 & "&b=" & Day(StartDate) & _ 
        "&c=" & Year(StartDate) & "&d=" & Month(EndDate) - 1 & "&e=" & _ 
        Day(EndDate) & "&f=" & Year(EndDate) & "&g=" & DataSheet.Range("E3") & "&q=q&y=0&z=" & _ 
        Symbol & "&x=.csv" 
        DataSheet.Range("A7") = qurl 
         
    QueryQuote: 
        With DataSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("A32")) 
            .BackgroundQuery = True 
            .TablesOnlyFromHTML = False 
            .Refresh BackgroundQuery:=False 
            .SaveData = True 
        End With 
         
        DataSheet.Range("A32").CurrentRegion.TextToColumns Destination:=DataSheet.Range("A32"), DataType:=xlDelimited, _ 
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ 
        Semicolon:=False, Comma:=True, Space:=False, other:=False 
         
        DataSheet.Range(DataSheet.Range("A32"), DataSheet.Range("A32").End(xlDown)).NumberFormat = "mm/dd/yy" 
        DataSheet.Range(DataSheet.Range("B32"), DataSheet.Range("B32").End(xlDown)).NumberFormat = "0.00" 
        DataSheet.Range(DataSheet.Range("C32"), DataSheet.Range("C32").End(xlDown)).NumberFormat = "0,000" 
        DataSheet.Range(DataSheet.Range("D32"), DataSheet.Range("D32").End(xlDown)).NumberFormat = "0.00" 
    
        next datasheet
    End Sub
    This could be cleaned up a bit, using a with statement, or a few other items. Change the "if ucase(datasheet.name) = " to match the sheet you will be running the macro from, I assumed it would be Main.

    And yes, I would put this in a new module.

    Comment


    • #3
      Re: Excel 2007 VBA - Multiple Macros

      Ok, so from your code i see you primarily changed the sheet identification. Would this be to solve my problem that the macro doesn't run unless I am actually viewing the sheet the macro is applied towards? Thanks!

      Comment


      • #4
        Re: Excel 2007 VBA - Multiple Macros

        Yes, the problem you were having was that the Range object was being applied to whatever the active sheet was. So, when the macro was run on Sheet2, the Range would apply to Sheet2. When it was run from Main, Range would apply to Main. In some places you were qualifying Range, using DataSheet, and DataSheet.Range would always point to the right worksheet. But, it wasn't consistently applied.

        Comment


        • #5
          Re: Excel 2007 VBA - Multiple Macros

          Where does my "End If" go? I try placing it in different areas, but I always get an error unless I put it right above "Next Worksheet". If I test what I have now, nothing happens. Here's what I have now.

          Code:
          Sub Holding1()
              Dim DataSheet As Worksheet
              Dim EndDate As Date
              Dim StartDate As Date
              Dim Symbol As String
              Dim qurl As String
              Dim nQuery As Name
               
              Application.ScreenUpdating = False
              Application.DisplayAlerts = False
              Application.Calculation = xlCalculationManual
               
              For Each DataSheet In ThisWorkbook.Worksheets
                  If UCase(DataSheet.Name) = "tester" Then
                       '    Set DataSheet = Sheet2
                  
                      StartDate = DataSheet.Range("B3").Value
                      EndDate = DataSheet.Range("B4").Value
                      Symbol = DataSheet.Range("E10").Value
                      DataSheet.Range("A32").CurrentRegion.ClearContents
                   
                      qurl = "http://ichart.yahoo.com/table.csv?s=" & Symbol
                      qurl = qurl & "&a=" & Month(StartDate) - 1 & "&b=" & Day(StartDate) & _
                      "&c=" & Year(StartDate) & "&d=" & Month(EndDate) - 1 & "&e=" & _
                      Day(EndDate) & "&f=" & Year(EndDate) & "&g=" & DataSheet.Range("E3") & "&q=q&y=0&z=" & _
                      Symbol & "&x=.csv"
                      DataSheet.Range("A7") = qurl
                
          QueryQuote:
                      With DataSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("A32"))
                          .BackgroundQuery = True
                          .TablesOnlyFromHTML = False
                          .Refresh BackgroundQuery:=False
                          .SaveData = True
                      End With
                       
                      DataSheet.Range("A32").CurrentRegion.TextToColumns Destination:=DataSheet.Range("A32"), DataType:=xlDelimited, _
                      TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                      Semicolon:=False, Comma:=True, Space:=False, other:=False
                       
                      DataSheet.Range(DataSheet.Range("A32"), DataSheet.Range("A32").End(xlDown)).NumberFormat = "mm/dd/yy"
                      DataSheet.Range(DataSheet.Range("B32"), DataSheet.Range("B32").End(xlDown)).NumberFormat = "0.00"
                      DataSheet.Range(DataSheet.Range("C32"), DataSheet.Range("C32").End(xlDown)).NumberFormat = "0,000"
                      DataSheet.Range(DataSheet.Range("D32"), DataSheet.Range("D32").End(xlDown)).NumberFormat = "0.00"
                    End If
                  Next DataSheet
              End Sub
          Last edited by MPierce486; November 8th, 2012, 05:12. Reason: Correction

          Comment


          • #6
            Re: Excel 2007 VBA - Multiple Macros

            Sorry, that is what I get for trying to do this in a browser. You put it in the right place.

            It looks like you have worksheet named "tester", the UCASE function will convert that to "TESTER" and your comparison will fail. You need to change the line to:

            Code:
            If UCase(DataSheet.Name) = "TESTER" Then
            and it should execute the code. I'm interested in how this works out for you, I've tried pulling information from the internet automatically before with less than satisfying results. This did actually pull some information for me when I tested it (not in this module), I need to play with it some more!

            Comment


            • #7
              Re: Excel 2007 VBA - Multiple Macros

              Still having problems. Here's the code I have, I only changed what you just identified.

              Code:
              Sub Holding1()
                  Dim DataSheet As Worksheet
                  Dim EndDate As Date
                  Dim StartDate As Date
                  Dim Symbol As String
                  Dim qurl As String
                  Dim nQuery As Name
                   
                  Application.ScreenUpdating = False
                  Application.DisplayAlerts = False
                  Application.Calculation = xlCalculationManual
                   
                  For Each DataSheet In ThisWorkbook.Worksheets
                      If UCase(DataSheet.Name) = "TESTER" Then
                           '      Set DataSheet = Sheet2
                           
                          StartDate = DataSheet.Range("B3").Value
                          EndDate = DataSheet.Range("B4").Value
                          Symbol = DataSheet.Range("E10").Value
                          DataSheet.Range("A32").CurrentRegion.ClearContents
                           
                          qurl = "http://ichart.yahoo.com/table.csv?s=" & Symbol
                          qurl = qurl & "&a=" & Month(StartDate) - 1 & "&b=" & Day(StartDate) & _
                          "&c=" & Year(StartDate) & "&d=" & Month(EndDate) - 1 & "&e=" & _
                          Day(EndDate) & "&f=" & Year(EndDate) & "&g=" & DataSheet.Range("E3") & "&q=q&y=0&z=" & _
                          Symbol & "&x=.csv"
                          DataSheet.Range("A7") = qurl
                           
              QueryQuote:
                          With DataSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("A32"))
                              .BackgroundQuery = True
                              .TablesOnlyFromHTML = False
                              .Refresh BackgroundQuery:=False
                              .SaveData = True
                          End With
                           
                          DataSheet.Range("A32").CurrentRegion.TextToColumns Destination:=DataSheet.Range("A32"), DataType:=xlDelimited, _
                          TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                          Semicolon:=False, Comma:=True, Space:=False, other:=False
                           
                          DataSheet.Range(DataSheet.Range("A32"), DataSheet.Range("A32").End(xlDown)).NumberFormat = "mm/dd/yy"
                          DataSheet.Range(DataSheet.Range("B32"), DataSheet.Range("B32").End(xlDown)).NumberFormat = "0.00"
                          DataSheet.Range(DataSheet.Range("C32"), DataSheet.Range("C32").End(xlDown)).NumberFormat = "0,000"
                          DataSheet.Range(DataSheet.Range("D32"), DataSheet.Range("D32").End(xlDown)).NumberFormat = "0.00"
                      End If
                  Next DataSheet
              End Sub
              I get the Type Mismatch error on
              Code:
              EndDate = DataSheet.Range("B4").Value

              Comment


              • #8
                Re: Excel 2007 VBA - Multiple Macros

                Do you want to pull dates from the datasheet, or the main sheet? The code is assuming you want to pull the dates from the data sheet. If you want to retrieve the dates from the main sheet, change the line to something like:

                Code:
                StartDate = Thisworkbook.worksheets("Main").Range("B3").Value
                If you want to use dates from the DataSheet, but need to validate, you could do something like this:

                Code:
                    if isdate(Datasheet.range("B3").value) _
                    and isdate(datasheet.range("B4").value) then
                .
                .
                .
                
                    else
                         msgbox "Data Sheet " & datasheet.name & " has an invalid date range", vbokonly
                    endif

                Comment


                • #9
                  Re: Excel 2007 VBA - Multiple Macros

                  Both the start and end dates on this "sheet2" are cell references to dates I inputted on the "main" sheet. Instead of using the references I can attempt the VB code. I'll try.

                  Comment

                  Working...
                  X