akamax_power
11-12-2009, 06:09 PM
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
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