CODEWITHSUNDEEP
excelvba
Ong K.S
Ong K.S

Reputation: 297

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

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.

enter image description here

Please advise what could be the reason of error.

Upvotes: 1

Views: 1161

Answers (1)

Ike
Ike

Reputation: 13024

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.

Upvotes: 4

Related Questions