Copying data from one workbook to the next. Error: Select Method of Range Class Failed

Question

Trying to copy data from one workbook to the next.

When I try to select a worksheet I get a message

Select Method of Range Class Failed

I want to use select to copy paste special something to keep the formatting.

Public Sub Worksheet_Export()
    
    'Setting Dimensions for Current Workbook and New workbook 
    
    Dim current_workbook As Workbook
    Dim New_workbook As Workbook
    
    Dim current_worksheet As Worksheet
    Dim New_worksheet As Worksheet
    
    Set current_workbook = ThisWorkbook
    Set New_workbook = Workbooks.Add
    
    Set current_worksheet = current_workbook.Sheets(2)
    Set New_worksheet = New_workbook.Sheets(1)
    
    'Copying Data From Current Workbook to CSV File Workbook
    
    current_worksheet.Range("A:C").Select

End Sub

The workbook containing the sheet needs to be active. You don't need to select to copy and paste though, you can just use the range object itself. — Warcupine, Dec 03 '21 at 15:26
Not only does the workbook need to be active but the sheet on which the range you are trying to select must be active. — Scott Craner, Dec 03 '21 at 15:27
BTW as @Warcupine mentioned, there are very few times one should use `.Select` or `.Activate` it only slows the code. Please see this canonical thread: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Scott Craner, Dec 03 '21 at 15:34
Here's an example of what they're talking about `Workbooks("MyBook1.xlsx").Sheets("Sheet1").Range("A1").Copy Destination:=Workbooks("MyBook2.xlsm").Sheets("Master").Range("G7")`. This skips 6 lines of `Thing.Select` and just directly copies the cells from one book to the other book. It is faster, since we skip the pauses after each `Select`, and it is cleaner since there are no books flashing on screen for 1 frame. — Toddleson, Dec 03 '21 at 15:47

score 0 · Answer 1 · answered Dec 04 '21 at 13:56

you need to activate the sheet before selecting the range

better solution:

current_worksheet.Range("A:C").Copy Destination:=New_worksheet.Range("A1")

Public Sub Worksheet_Export()

'Setting Dimensions for Current Workbook and New workbook

Dim current_workbook As Workbook
Dim New_workbook As Workbook

Dim current_worksheet As Worksheet
Dim New_worksheet As Worksheet

Set current_workbook = ThisWorkbook
Set New_workbook = Workbooks.Add

Set current_worksheet = current_workbook.Sheets(2)
Set New_worksheet = New_workbook.Sheets(1)




'Copying Data From Current Workbook to CSV File Workbook
current_worksheet.Activate 'you need to activate before selecting
current_worksheet.Range("A:C").Select



'better solution:
current_worksheet.Range("A:C").Copy Destination:=New_worksheet.Range("A1")

End Sub

Copying data from one workbook to the next. Error: Select Method of Range Class Failed

1 Answers1