Import range from closed excel file

[akamax_power]

I'm trying to import data from 3 different sheets from a closed workbook to a new workbook. I keep getting an error when it gets to the copy part of the formula. I'm not sure what I'm missing

[vba]Sub Import()
Dim Filename As String
Dim Wb As Workbook
Dim start, ctend, phend, estend As Range


Filename = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xls),*.xls", _
Title:="Pick File to Import From", _
MultiSelect:=False)
If Filename = "False" Then Exit Sub

Set Wb = Workbooks.Open(Filename)
Set start = Range("A2")
Set ctend = Wb.Sheets("Contract").Range("G65536").End(xlUp)
Set phend = Wb.Sheets("Phase").Range("C65536").End(xlUp)
Set estend = Wb.Sheets("PhaseEST").Range("F65536").End(xlUp)

'//Problem Starts here

Wb.Sheets("Contract").Range(start, ctend).Copy
ThisWorkbook.Worksheets("Contract").Range("A65536").End(xlUp).PasteSpecial xlPasteValues
Application.CutCopyMode = False

Wb.Worksheets("Phase").Range(start, phend).Copy
ThisWorkbook.Worksheets("Phase").Range("A65536").End(xlUp).PasteSpecial xlPasteValues
Application.CutCopyMode = False

Wb.Worksheets("PhaseEST").Range(start, estend).Copy
ThisWorkbook.Worksheets("PhaseEST").Range("A65536").End(xlUp).PasteSpecial xlPasteValues
Application.CutCopyMode = False

Wb.Close Savechanges:=False


End Sub
[/vba]

[GTO]

Greetings,

I did not test, but in looking I think I see a couple of issues.

[vba]Set start = Range("A2")[/vba]

Given that the workbook you just opened will be the active one, I believe you'll be setting 'start' to A2 of whatever sheet happens to be active...

Thus - after setting ranges to a cell in in the specified sheets...
[vba]
Set ctend = Wb.Sheets("Contract").Range("G65536").End(xlUp)
Set phend = Wb.Sheets("Phase").Range("C65536").End(xlUp)
Set estend = Wb.Sheets("PhaseEST").Range("F65536").End(xlUp)
[/vba]

'start' and 'ctend' may be cells on different sheets (which excel won't be all giddy about).
[vba]
Wb.Sheets("Contract").Range(start, ctend).Copy
[/vba]

Now since we're not really needing to set the ending range/cell, see if the following works out.

Again, not tested, so in a junk copy of your wb...

In a Standard Module:
[vba]
Sub Import()
Dim Filename As String
Dim Wb As Workbook

'// If these are to be ranges, ...
'Dim start, ctend, phend, estend As Range
'// ...they need declared like:
'Dim start As Range, ctend As Range, phend As Range, estend As Range

Dim lStart As Long, lCtEnd As Long, lPhEnd As Long, lEstEnd As Long

Filename = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xls),*.xls", _
Title:="Pick File to Import From", _
MultiSelect:=False)
If Filename = "False" Then Exit Sub

Set Wb = Workbooks.Open(Filename)

lStart = 2
lCtEnd = Wb.Sheets("Contract").Range("G" & Rows.Count).End(xlUp).Row
lPhEnd = Wb.Sheets("Phase").Cells(Rows.Count, 3).End(xlUp).Row
lEstEnd = Wb.Sheets("PhaseEST").Cells(Rows.Count, "F").End(xlUp).Row

'// Try like:
'// (NOTE: you may want to include the .Offset to drop a row below the last value //

Wb.Sheets("Contract").Range("A2:G" & lCtEnd).Copy
ThisWorkbook.Worksheets("Contract").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
'...remainder of statements...
End Sub
[/vba]

Does that help?

Mark