You are here:

Excel/Combining data from multiple workbooks into one worksheet


Excel 2010
Hi Tom,

I have been using a macro you provided for an answer to this question back on 6-29-07.
The macro works great in Excel 2003 but we just got Excel 2010 and Iím getting an Application-defined or object-defined error when it gets to this line
Set rng1 = sh.Cells(Rows.Count, 1).End(xlUp)(2)

Could you please help me fix this and explain why it fails in Excel 2010?
Thanks, Anthony

Sub ImportFile()
Dim x As Long
Dim z As Variant
Dim bk As Workbook
Dim sh As Worksheet
Dim sh1 As Worksheet
'Change the next line to reflect the propername and workbook where the data will be consolidated
   Set sh = Workbooks("Daily_Detail.xls").Worksheets("Detail_Import")
       z = Application.GetOpenFilename _
       (FileFilter:="detail (*.xls), *.xls", MultiSelect:=True)
         If Not IsArray(z) Then
         MsgBox "Nothing selected"
 Exit Sub
End If

'Open loop for action to be taken on all selected workbooks.
   For x = 1 To UBound(z)

'Open the workbook(s) that were selected.
   Set bk = Workbooks.Open(z(x))

'Check if sheet exists
   On Error Resume Next
       Set sh1 = bk.Worksheets("Detail") 'Detail is the name of the sheet to Import
         On Error GoTo 0          
' if it exists, copy the data
   If Not sh1 Is Nothing Then
       Set Rng = sh1.Range("A1:IV25001") 'range that will be imported from detail.xls          
         Set rng1 = sh.Cells(Rows.Count, 1).End(xlUp)(2)
         rng1.PasteSpecial xlValues
  'rng1.Pastespecial xlFormats (add this if you want to paste the formats)
   End If
'Close the the workbook(s)you selected to pull the data from without saving them.
        bk.Close False

         Next x          

'Message box to inform user the job is complete.
   MsgBox "The import is complete.", 64, "Done !!"
End Sub


I would expect it to work as written - but if it is a problem, then I would suggest changing

Set rng1 = sh.Cells(Rows.Count, 1).End(xlUp)(2)

to be

Set rng1 = sh.Cells(sh.Rows.Count, 1).End(xlUp)(2)

so that rows.count is qualified with sh.  

You also might try recompiling the file although making that change should cause it to recomile anyway.

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.