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

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

[GTO]

Greetings,

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

Set start = Range("A2")

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...

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)


'start' and 'ctend' may be cells on different sheets (which excel won't be all giddy about).

Wb.Sheets("Contract").Range(start, ctend).Copy


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:

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


Does that help?

Mark