Error Select Method of Range Class Failed

Question

My code aims to import an .xls file selected by the user, and copy and paste it into my Data sheet in Book 1. This Book 1 has 2 sheets: Results and Data.

I want to run the code when I am in Results and here comes the problem. When I run it in my Data sheet, after clearing the current sheet (Data) the file is imported and copied well.

However, when I import it when I am in the Results sheet, it comes an error according to the MsgBox Err.Description What's wrong in the code?

Sub ImportData()

Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook
Dim wb As Workbook

On Error Resume Next

Set wb = Application.Workbooks("Book1.xlsm")
wb.Activate
wb.Sheets("Data").Range("A1:M5000").Select
Selection.ClearContents
Sheets("Data").Select
Range("A1").Select

Set targetWorkbook = Application.ActiveWorkbook

filter = "Text files (*.xls),*.xls"
caption = "Please Select an Input File "
customerFilename = Application.GetOpenFilename(filter, , caption)

Set customerWorkbook = Application.Workbooks.Open(customerFilename)

Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.ActiveSheet
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.ActiveSheet

sourceSheet.UsedRange.Copy targetSheet.Range("A1")
sourceSheet.UsedRange.Value = sourceSheet.UsedRange.Value
targetSheet.Paste

customerWorkbook.Saved = True
customerWorkbook.Close

Sheets("Results").Select

End Sub

Activate you Data sheet first. BTW, try to avoid using select since it's highly related to your screen and drag down the performance. — newacc2240, Nov 10 '17 at 08:57
see https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba for more info — newacc2240, Nov 10 '17 at 09:01
many thanks @newacc2240 how do I do at? I am pretty newie with VBA. Best regards, — Rose, Nov 10 '17 at 09:01

Ryszard Jędraszyk · Answer 1 · 2017-11-10T09:12:42.973

The following code:

wb.Sheets("Data").Range("A1:M5000").Select
Selection.ClearContents

Should be replaced with:

wb.Sheets("Data").Range("A1:M5000").ClearContents

The same goes for all similar lines. Operating on selection most often comes from macro recorder, is a very unreliable and slow method. It's dependent on currently selected range or object and non-transparent in the code, as it forces to know what is currently selected / active.

Selecting should be done only to leave selected worksheet or cell active in the end of macro operation or to perform actions on ActiveWindow.

Likewise, try to eliminate ActiveSheet:

Set targetSheet = targetWorkbook.ActiveSheet

And replace it with one of the following examples:

Set targetSheet = targetWorkbook.Worksheets(1) '1st worksheet in the file
Set targetSheet = targetWorkbook.Worksheets("myData") 'worksheet named "myData"

also the `sourceSheet`, it's dangerous to set it to `ActiveSheet` since you won't know the active one is the sheet you need or not. — newacc2240, Nov 10 '17 at 09:11

score 0 · Accepted Answer · answered Nov 10 '17 at 09:12

You may try it like this...

Sub ImportData()
Dim filter As String
Dim caption As String
Dim customerFilename As Variant
Dim customerWorkbook As Workbook, targetWorkbook As Workbook
Dim targetSheet As Worksheet, sourceSheet As Worksheet

Set targetWorkbook = Application.Workbooks("Book1.xlsm")
Set targetSheet = targetWorkbook.Sheets("Data")
targetSheet.Range("A1:M5000").ClearContents

filter = "Text files (*.xls),*.xls"
caption = "Please Select an Input File "
customerFilename = Application.GetOpenFilename(filter, , caption)

If customerFilename = False Then
    MsgBox "No Customer File was selected.", vbExclamation
    Exit Sub
End If
Set customerWorkbook = Application.Workbooks.Open(customerFilename)

Set sourceSheet = customerWorkbook.ActiveSheet

sourceSheet.UsedRange.Copy
targetSheet.Range("A1").PasteSpecial xlPasteValues

Application.CutCopyMode = 0

sourceSheet.UsedRange.Value = sourceSheet.UsedRange.Value

customerWorkbook.Saved = True
customerWorkbook.Close

targetWorkbook.Sheets("Results").Select

End Sub

Error Select Method of Range Class Failed

2 Answers2