Macro Button on Diff WS

[Landzir101]

Fairly new to macros and I was how I would go about having a web query (that is executed by a button) execute on a separate worksheet than the active sheet it is on. This is a brief summary of what I am doing:

I have about a dozen worksheets that are formatted for a specific web query. On each page I have a macro assigned to a button. So, to get the data I go through each tab and click the button; it pulls the data through.

What I am wanting is a way to have the button separate from the worksheet (say on the main page) and be able to query all the data from that page. Not sure on the exact naming convention or way to do this. Thanks for the help!

Below is a little of the code so you can see what I am saying...

...
Sub Annual_Income_Statement()
Dim sSymbol As String
Dim sDestination As String

With ActiveSheet
.Range("B4:M200").ClearContents
sSymbol = .Range("B3").Value
sDestination = .Range("B3").Offset(2, 0).Address
With .QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/is?s=" & sSymbol & "&annual", Destination:=Range(sDestination))
'.PostText = "Quote0=" & sSymbols
.Name = "Recent News " & sSymbol

 

[VoG]

Hello and welcome to MrExcel.

Instead of

With ActiveSheet

try

With Sheets("Sheet1")

Change Sheet1 to the name of the sheet.

 

[Landzir101]

It is throwing an error saying that the destination range is not on the same worksheet that the Query table is being created on....help on how to code the destination range to the sheet name, cause I only have it directed to the active sheet currently right?

 

[Bill Rockenbach]

Something like this Put the code in a standard module and teh button on a sheet named "Main". Queries all teh sheets

Option Explicit
Sub Annual_Income_Statement()
    Dim sSymbol As String
    Dim sDestination As String
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        If ws.Name <> "Main" Then
            ws.Select
            With ActiveSheet
                .Range("B4:M200").ClearContents
                sSymbol = .Range("B3").Value
                sDestination = .Range("B3").Offset(2, 0).Address
                With .QueryTables.Add(Connection:= _
                                      "URL;http://finance.yahoo.com/q/is?s=" & sSymbol & "&annual", Destination:=Range(sDestination))
                    '.PostText = "Quote0=" & sSymbols
                    .Name = "Recent News " & sSymbol
                End With
            End With
        End If
    Next ws
    Sheets("Main").Select
End Sub

 

[VoG]

This should work - the dots qualify the ranges to Sheet1

With Sheets("Sheet1")
    .Range("B4:M200").ClearContents
    sSymbol = .Range("B3").Value
    sDestination = .Range("B3").Offset(2, 0).Address
    With .QueryTables.Add(Connection:= _
        "URL;http://finance.yahoo.com/q/is?s=" & sSymbol & "&annual", Destination:=Range(sDestination))
        '.PostText = "Quote0=" & sSymbols
        .Name = "Recent News " & sSymbol

 

[Landzir101]

Thanks! Looks like it is exactly what I need just need to implement all of it.

 

[Landzir101]

Worked perfectly guys....but now i'm trying to go a little further into the process. I was wondering how/if you can make a button from one workbook that updates all the others (or at least some of them).

What I have: I have 420 different workbooks that each have buttons in them that update the worksheets in their respective workbooks. I need to update these once a quarter about. I was wanting to set up a MAJOR button that from one master workbook I can click and it goes about updating all the 420 others.

Is this possible? If it is, I am interested to know the key it (like defining workbooks within the macro button, etc.) and if any of my code so far would help I would be more than happy to post and work with you guys! Thanks!