Select Method of WorkSheet class failed when trying to select a worksheet

Question

I build VBA that open an Excel Workbook, select specific Worksheet based on name, copy the sheet into new Workbook with code below.

Dim myBook As Workbook
    Dim sheet As Worksheet
    Dim targetSheet1, targetSheet2, targetName1, targetName2, targetDir As String
    
    targetDir = "C:\Users\kamsiong.ong\Desktop\temp"
    
    targetSheet1 = "Uob(Airpay)Wallet Paid"
    targetName1 = targetDir + "\" + targetSheet1 + ".csv"
    
    targetSheet2 = "Uob(Airpay)Offline Payment"
    targetName2 = targetDir + "\" + targetSheet2 + ".csv"
    
    
    Set myBook = Workbooks.Open("C:\Users\kamsiong.ong\Desktop\temp\Daily Financial Report Tool -12092021")
    
    For Each sheet In myBook.Worksheets
    
        If sheet.Name = targetSheet1 Then
            
            myBook.Sheets(targetSheet1).Select # Error
            myBook.Sheets(targetSheet1).Copy
            ChDir targetDir
            ActiveWorkbook.SaveAs Filename:=targetName1, FileFormat:=xlCSVUTF8, CreateBackup:=False
            
        ElseIf sheet.Name = targetSheet2 Then
        
            myBook.Sheets(targetSheet2).Select
            myBook.Sheets(targetSheet2).Copy
            ChDir targetDir
            ActiveWorkbook.SaveAs Filename:=targetName2, FileFormat:=xlCSVUTF8, CreateBackup:=False
                
        End If
        
    Next sheet
    
    ActiveWindow.Close
    Set myBook = Nothing

However, my program hit a bug that says Select Method of WorkSheet class failed within the For-Loop when I select Worksheet by name myBook.Sheets(targetSheet1).Select.

Please advise what could be the reason of error.

Please, comment the lines `myBook.Sheets(targetSheet1).Select` and `myBook.Sheets(targetSheet1).Copy` and use instead `sheet.Copy`... Probably, the workbook keeping the sheet in discussion was not activated, but selecting/activating, only consumes Excel resources and do not bring any benefit. — FaneDuru, Oct 18 '21 at 09:18
Stop using `Select` (see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba)). This is a bad practice and you easily run into errors. • Note that you can only select a worksheet if the workbook is the active one. But I really recommend not using `Select` and do as FaneDuru explained. • Also avoid `ActiveWorkbook` and `ActiveWindow` both times you can use `myBook` instead. — Pᴇʜ, Oct 18 '21 at 09:37
While this isn't the cause of your problem - Please also note that `Dim targetSheet1, targetSheet2, targetName1, targetName2, targetDir As String` will only declare `targetDir` as `String` while the rest are declared as `Variant`, you have to declare each variable's type explicitly e.g. `Dim targetSheet1 As String, targetSheet2 As String, targetName1 As String, targetName2 As String, targetDir As String` — Raymond Wu, Oct 18 '21 at 09:41

Ike · Answer 1 · 2021-10-18T10:04:33.870

4

The sheet is hidden - that's why it can't be selected. See last entry of properties-screenshot.

As others already stated: select is not necessary to copy the sheet. You could simply use myBook.Sheets(targetSheet1).Copy without the select.

But a hidden sheet can't be copied - so unless you make it visible you will have the error in the copy-row.

edited Oct 18 '21 at 10:04

answered Oct 18 '21 at 09:44

Ike

9,580
4
13
29

1

One more reason to stay away from `.Select` :) Well spotted! – Pᴇʜ Oct 18 '21 at 09:57
1

A side note: **One sheet must be visible.** A **single** sheet cannot be copied to a **new** workbook if it is hidden or very hidden. A **single** sheet can be copied to **another** workbook even if it is hidden, but not if it is very hidden. **Multiple** sheets can be copied to a **new** workbook as long as one of them is visible (the order, e.g. in an array, is not relevant ), although any very hidden sheets will not be copied (no error). **Multiple** sheets can be copied to **another** workbook even if they all are hidden. Again, if any are very hidden, they will not be copied (no error). – VBasic2008 Oct 18 '21 at 16:44

Select Method of WorkSheet class failed when trying to select a worksheet

1 Answers1