Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Excel 2007 VBA - Multiple Macros

  1. #1
    Join Date
    7th November 2012
    Posts
    5

    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.

    VB:
    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 = "[URL]http://ichart.yahoo.com/table.csv?s[/URL]=" & 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.

    VB:
    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 at 04:31. Reason: Update

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th November 2012
    Posts
    40

    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:

    VB:
    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 = "[URL]http://ichart.yahoo.com/table.csv?s[/URL]=" & 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.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    7th November 2012
    Posts
    5

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th November 2012
    Posts
    40

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    7th November 2012
    Posts
    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.

    VB:
    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 = "[URL]http://ichart.yahoo.com/table.csv?s[/URL]=" & 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 at 04:12. Reason: Correction

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    7th November 2012
    Posts
    40

    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:

    VB:
    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!

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    7th November 2012
    Posts
    5

    Re: Excel 2007 VBA - Multiple Macros

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

    VB:
    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 = "[URL]http://ichart.yahoo.com/table.csv?s[/URL]=" & 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
    VB:
    EndDate = DataSheet.Range("B4").Value 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    7th November 2012
    Posts
    40

    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:

    VB:
    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:

    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    7th November 2012
    Posts
    5

    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Convert macros developed in Excel 2003 to Excel 2007
    By Rajesh Pathangi in forum EXCEL HELP
    Replies: 1
    Last Post: March 8th, 2011, 00:36
  2. Enable but not run macros in Excel 2007
    By areinmeyer in forum EXCEL HELP
    Replies: 3
    Last Post: August 31st, 2010, 23:41
  3. Excel Macros (2003 to 2007)
    By Dave Hawley in forum Office 2007 Migration and Application Compatibility
    Replies: 0
    Last Post: July 3rd, 2009, 18:55

Bookmarks

Posting Permissions

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