-
Import range from closed excel file
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]
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules