How To Resize Data Source For Pivot w/VBA

[mschles4695]

I have the following VBA code which on click of a button pulls data from a SQL Server and populates an excel worksheet tab (same # of columns, potentially different # of rows). Everything works EXCEPT I don't know how in code to dynamically change the Data->Change Data Source->(cell range) to match the range of the newly imported data. Currently I have just set this range to A1:Z65000 which always works but gives me blanks in the pivot tables that are based on this data. HELP PLEASE!!!!

Dim Cn As Variant
Set Cn = CreateObject("ADODB.Connection")
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As Variant
Set rs = CreateObject("ADODB.Recordset")
Dim pt As PivotTable
Dim ws As Worksheet

Server_Name = "xxxxxxx" ' Enter your server name here
Database_Name = "yyyyyyy" ' Enter your database name here
User_ID = "dddddddd" ' Enter your user ID here
Password = "rrrrrrrr" ' Enter your password here
SQLStr = "SELECT EM.em_id, EM.em_std, EM.em_manager, EM.comments, " & _
" RM.rm_cat, RM.rm_type, RM.rm_std, RM.area, RM.fl_id, RM.rm_id, RM.dp_id, RM.dv_id, " & _
" BL.bl_id, BL.raa_site, " & _
" S.name AS Site " & _
"FROM afm.em EM " & _
" LEFT JOIN afm.rm RM ON (EM.fl_id = RM.fl_id) AND (EM.rm_id = RM.rm_id) AND (EM.bl_id = RM.bl_id) " & _
" LEFT JOIN afm.bl BL ON (RM.bl_id = BL.bl_id) " & _
" LEFT JOIN afm.site S ON (BL.site_id = S.site_id) " & _
"WHERE RM.rm_cat ='OFFICE' AND " & _
" ( RM.rm_type Not Like 'VISIT%' And " & _
" RM.rm_type Not Like 'OPEN%' And " & _
" RM.rm_type Not Like 'FLEX%' And " & _
" RM.rm_type Not Like 'FILE%' And " & _
" RM.rm_type Not Like 'OFF-%' And " & _
" RM.rm_type Not Like 'WRKAREA%') "

Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"

rs.Open SQLStr, Cn
' Data Dump to spreadsheet
With Worksheets("Data").Range("A2:Z65000") .ClearContents
.CopyFromRecordset rs
End With
' Update Pivot Tables
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables

HERE IS WHERE I NEED CODE THAT RESIZES THE NEWLY IMPORTEDSOURCEDATA RANGE

pt.RefreshTable
Next pt
Next ws
'
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing