Subscript Out of Range

[imfarhan]

Error: 9 - Subscript out of range

I have written the following code , it was working before on my Test Excel sheet when I tried to run on the live xl Sheets i.e. Source and Destination its giving me above error on the line(red font) given below:

Aprreciate your help. The code is written in Acess , but mainly working on Excel Sheet.
Many thanks
Farhan

   Dim wbk1 As Workbook
   Dim wbk2 As Workbook
   Dim sourceFile As String
   Dim rst As DAO.Recordset
   
    
      Set rst = CurrentDb.OpenRecordset("qryPBRAuditReport", dbOpenDynaset, dbSeeChanges)
    
    sourceFile = rst.Fields("FinalReportName").Value
    'Debug.Print sourceFile
    'sourceFile = "T:\Info_Team\Farhan\Reports\Readmission audit report\PBR ReAdmission Audit Report - 20110920 1406.xls"
    'sourceFile = "T:\Info_Team\Farhan\Reports\Readmission audit report\" & sourceFile
    sourceFile = "N:\Reporting Warehouse\HRG 4\Useful Documents\2011-12\Test\" & sourceFile
    Const destinationFile As String = "N:\Reporting Warehouse\HRG 4\Useful Documents\2011-12\Test\Readmission audit JT access upload do not touch.xls"
                                                                                          
    
    Set wbk1 = Workbooks.Open(sourceFile)
    Set wbk2 = Workbooks.Open(destinationFile)
    
    [COLOR=red]wbk1.Sheets("Audit_Sheet_for_Input").Range("E:E").Copy[/COLOR] Destination:=wbk2.Sheets("JT access upload do not touch").Range("A1")
    wbk1.Sheets("Audit_Sheet_for_Input").Range("F:F").Copy Destination:=wbk2.Sheets("JT access upload do not touch").Range("B1")
    wbk1.Sheets("Audit_Sheet_for_Input").Range("W:W").Copy Destination:=wbk2.Sheets("JT access upload do not touch").Range("C1")
                 
    wbk2.Sheets("JT access upload do not touch").Rows("1:5").Select
    Selection.Delete Shift:=xlUp
    
    wbk1.Close
    wbk2.Save
    wbk2.Close
    LogUpdate "Excel WorkBook Selected Columns has has been copied...... "

Err_Handler: 'Need to check why Error 0 Generate?
   LogUpdate "Error: " & Err.Number & " - " & Err.Description & " -  CopyExcelSheet()"
     'CopyExcelSheet() = FunctionFailed
       
End Sub

 

[SydneyGeek]

If the workbook doesn't have that exact worksheet, you will get the error you described.

Denis

 

[imfarhan]

I'm stupid
Thanks for your help Sydney
Regards
Farhan