0

I am getting a run-time error 1004 Application-defined or object-defined error. I have some VB in Excel that I am trying to get it to select a range (that possibly may change).. it is erroring out on 

 Set SourceRange = WorkBk.Sheets("Sheet8").Range("B14", Range("XFD14").End(XltoLeft).Offset(0,2))

Need to be able to have the SourceRange be range B14(always) to the last available cell + 2 to the right (for just in cases)

I have:

 Sub MergeData()
 Dim SourceRange as Range
 WorkBk As Workbook
 Dim DestRange as Range
 Dim FolderPath As String
 Dim FileName As String
 Dim NRow As Long
 FolderPath = Sheets("Sheet1").Range("D7")
 NRow = 4
 FileName = DIR(FolderPath & "*.xlsb")
 Do While FileName <> ""
 Set WorkBk = Workbooks.Open(FolderPath & FileName)
  WorkBk.Application.Run _
    "'" & FileName & "'!auto_open"
 Set SourceRange = WorkBk.Sheets("Sheet8").Range("B14", Range("XFD14").End(XltoLeft).Offset(0,2))
  Set DestRange = Sheets("Sheet2").Range("B" & NRow)
    Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
       SourceRange.Columns.Count)
  DestRange.Value = SourceRange.Value 
  NRow = NRow + DestRange.Rows.Count
 WorkBk.Close savechanges:=False
  Loop
Community
  • 1
  • 1
David Redden
  • 147
  • 1
  • 15

1 Answers1

1

The unqualified Range("XFD14")... refers to a cell on the active sheet, not necessarily the sheet you expected.

Change to

With WorkBk.Sheets("Sheet8")
     Set SourceRange = .Range("B14", .Range("XFD14").End(XltoLeft).Offset(0,2))
End With
chris neilsen
  • 52,446
  • 10
  • 84
  • 123