Importing external data to A1

**globulous** · 10-13-2009, 10:23 AM

I have 5 or 6 macros set up to import stats from the internet to specific sheets.
I want all of my imported data to be pasted starting at A1..

However when i run these macros, the data IS pasted at A1, but when i run it a second time (or the next day after the pages have been updated), it pastes the data BESIDE the previously pasted stuff...

here's what i mean..

say my imported data spans columns A to S..
everything's fine..

but tomorrow i want to update the data so i run the macro again..
well, it pastes the new data to A1 and again it spans from A to S like it's supposed to, but now it also tacks on the same paste job from S to, AG for instance...

here's the code... There are currently 5 macros that have this problem...
i thought by selecting A1 as the starting point, i could alleviate all of this but apparently not.

Sub GOALSbyPeriod()
'
' GOALSbyPeriod Macro
' Macro recorded 10/11/2009 by ...
'

'
    Sheets("Goals By Period").Select
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://scoreboards.canoe.ca/merge/tsnform.aspx?c=canoe&page=/nhl/stat/nhl-goals-by-period.htm" _
        , Destination:=Range("A1"))
        .Name = "nhl-goals-by-period_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingRTF
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .Refresh BackgroundQuery:=False
    End With
    Columns("A:B").Select
    Range("B1").Activate
    Selection.Delete Shift:=xlToLeft
    Rows("1:18").Select
    Range("A18").Activate
    Selection.Delete Shift:=xlUp
    ActiveWindow.SmallScroll Down:=74
    ActiveWindow.ScrollRow = 1
    ActiveWindow.SmallScroll Down:=45
    ActiveWindow.ScrollRow = 1
    Columns("A:A").ColumnWidth = 27.29
    Columns("A:A").ColumnWidth = 20
    ActiveWindow.SmallScroll Down:=88
    ActiveWindow.ScrollRow = 1
    ActiveWindow.SmallScroll ToRight:=3
    ActiveWindow.ScrollColumn = 1
    Columns("B:B").ColumnWidth = 3
    Columns("H:H").ColumnWidth = 3
    Columns("M:M").ColumnWidth = 3
    Columns("R:R").ColumnWidth = 3
    Columns("W:W").ColumnWidth = 3
    Range("A1").Select
End Sub
Sub OFFENSIVEstats()
'
' OFFENSIVEstats Macro
' Macro recorded 10/11/2009 by ...
'

'
    Sheets("OffenceStats").Select
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.sportsnet.ca/hockey/nhl/stats/team/scoring", Destination:= _
        Range("A1"))
        .Name = "scoring_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .Refresh BackgroundQuery:=False
    End With
    Rows("2:31").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A1").Select
End Sub
Sub SPECIALTEAMstats()
'
' SPECIALTEAMstats Macro
' Macro recorded 10/11/2009 by ...
'

'
    Sheets("SpecialTeams").Select
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.sportsnet.ca/hockey/nhl/stats/team/special", Destination:= _
        Range("A1"))
        .Name = "special"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .Refresh BackgroundQuery:=False
    End With
    Rows("2:31").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A1").Select
End Sub
Sub POWERPLAY()
'
' POWERPLAY Macro
' Macro recorded 10/11/2009 by ...
'

'
    Sheets("PowerPlay").Select
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://scoreboards.canoe.ca/merge/tsnform.aspx?c=canoe&page=/nhl/stat/nhl-pp-records.htm" _
        , Destination:=Range("A1"))
        .Name = "nhl-pp-records_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .Refresh BackgroundQuery:=False
    End With
    Columns("A:C").Select
    Range("C1").Activate
    Selection.Delete Shift:=xlToLeft
    Rows("1:18").Select
    Range("A18").Activate
    Selection.Delete Shift:=xlUp
    Range("A3:E32").Select
    Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("F3:J32").Select
    Selection.Sort Key1:=Range("F3"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("K3:O32").Select
    Selection.Sort Key1:=Range("K3"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A1").Select
End Sub
Sub PENALTYKILLING()
'
' PENALTYKILLING Macro
' Macro recorded 10/11/2009 by ...
'

'
    Sheets("PenaltyKilling").Select
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://scoreboards.canoe.ca/merge/tsnform.aspx?c=canoe&page=/nhl/stat/nhl-penalties.htm" _
        , Destination:=Range("A1"))
        .Name = "nhl-penalties"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .Refresh BackgroundQuery:=False
    End With
    Columns("A:B").Select
    Range("B1").Activate
    Selection.Delete Shift:=xlToLeft
    Rows("1:18").Select
    Range("A18").Activate
    Selection.Delete Shift:=xlUp
    Range("A2:E31").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    ActiveWindow.SmallScroll Down:=10
    ActiveWindow.ScrollRow = 1
    ActiveWindow.SmallScroll Down:=28
    Rows("33:36").Select
    Selection.Delete Shift:=xlUp
    Range("A33:A69").Select
    Columns("A:A").ColumnWidth = 18.29
    Range("A33:B69").Select
    Selection.Delete Shift:=xlToLeft
    Rows("33:33").Select
    Selection.Insert Shift:=xlDown
    ActiveWindow.SmallScroll Down:=15
    Range("A36:E65").Select
    Selection.Sort Key1:=Range("A36"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("G36:K65").Select
    Selection.Sort Key1:=Range("G36"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("M36:Q65").Select
    Selection.Sort Key1:=Range("M36"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A1").Select
End Sub

I just want it to make one paste job, where it pastes the new data directly over the old data...

currently it is doing this, but for some reason it is also pasting it starting at the first open column after it pastes over the old data..
so if i do this 5 days in a row, there will be 5 paste jobs on the same sheet, one after the other...

any thoughts??