hi, Ryan !

did you tried using ADO ? (there are several ways), here is an example:
get in an (empty ?) activesheet all the data in "UsedRange" of the worksheet (from closed Wbk)

(important): mark a reference in your vba project to:
Microsoft ActiveX Data Objects x.x Library (x.x should it be version 2.8 ?)

Sub GetDataFromClosedWorkbook_ADO()
Dim adoConn As ADODB.Connection, rstData As ADODB.Recordset, _
strSQL As String, srcFolder As String, srcFile As String, srcSheet As String
srcFolder = "c:\documents and settings\rshuell\desktop\weekly reporting\friday\"
srcFile = "weeklyforecastingall.xls"
srcSheet = "weeklyforecastingall"
Set adoConn = New ADODB.Connection
adoConn.Open "driver={microsoft excel driver (*.xls)};driverId=790;readonly=true;dbq=" & srcFolder & srcFile & ";"
strSQL = "select * from [" & srcSheet & "$]"
Set rstData = New ADODB.Recordset
On Error Resume Next
rstData.Open strSQL, adoConn, adOpenForwardOnly, adLockReadOnly, adCmdText
Range("a1").CopyFromRecordset rstData
If rstData.State = adStateOpen Then rstData.Close
Set rstData = Nothing
adoConn.Close
Set adoConn = Nothing
End Sub

hth,
hector.

__ OP __
I am using this code: http://www.rondebruin.nl/copy7.htm
Extremely useful!!
This is the sub I am using now:
Sub File_In_Local_Folder()
'***
End Sub

I am just wondering how to make the range in the closed Workbook variable. (...)

But, how do I get the variable rows, from the closed workbook, into the GetRange variable?
Am I just making this overly complicated? Is there an easier way?

__ exposed code __
I surmise that it would require something like this:
Sub File_In_Local_Folder()
Application.ScreenUpdating = False
On Error Resume Next
Dim Last As Long
Dim DestSh As Worksheet
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "WeeklyForecastingAll"
With ActiveSheet
.Move after:=Worksheets(Worksheets.Count)
End With
Last = LastRow(DestSh)
'Call the macro GetRange
GetRange "C:\Documents and Settings\rshuell\Desktop\Weekly Reporting\Friday", "WeeklyForecastingAll.xls", "WeeklyForecastingAll", Last = LastRow(DestSh), _
Sheets("WeeklyForecastingAll").Range("A1")
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Sub GetRange(FilePath As String, FileName As String, SheetName As String, _
SourceRange As String, DestRange As Range)
Dim Start
'Go to the destination range
Application.Goto DestRange
'Resize the DestRange to the same size as the SourceRange
Set DestRange = DestRange.Resize(Range(SourceRange).Rows.Count, _
Range(SourceRange).Columns.Count)
'Add formula links to the closed file
With DestRange
.FormulaArray = "='" & FilePath & "/[" & FileName & "]" & SheetName _
& "'!" & SourceRange
'Wait
Start = Timer
Do While Timer < Start + 2
DoEvents
Loop
'Make values from the formulas
.Copy
.PasteSpecial xlPasteValues
.Cells(1).Select
Application.CutCopyMode = False
End With
End Sub