Error 9 subscript out of range error in Access 2003

[vba317]

I am using Access 2003 and I am getting an error 9 subscript out of range error. What I am trying to do is copy four worksheets from one workbook to the end of another workbook. The source workbook has four sheets in it. The Destination workbook as 29 sheets in it. I get the error when I try to activate the original workbook. The getFolderInfo and the xlopen procedures work. The strAdhocRpt is the file name that I want. Any help is appreciated.

Tom

Public Function xlStandardReport(strUCI As String, strFileType As String)
    Dim strFileLoc As String
    Dim strClient As String
    Dim strFile As String
    Dim strSheet As String
    Dim strCmpy As String
    Dim strMon As String
    Dim iCalNum As Integer
    Dim iYr As Integer
    Dim x As Integer
    Dim strAdhocRpt As String
    Dim strStdRpt As String
    Dim strRptType As String
    Dim currentSheet As Worksheet
    Dim sheetIndex As Integer
    Call GetFolderInfo(strCmpy, strUCI, iYr, iCalNum, strMon)
    If strUCI = "AIS" Then strFileLoc = "\\salmfilesvr1\Public\Client Services\AutoRpts\_RptSets\" & strCmpy & "\" & strUCI & "\" & iYr & "\" & iCalNum & "\"
    If strUCI = "AIS" Then strFile = "SECURE EMAIL_" & strUCI & "_" & strMon & "_" & iYr & "_" & "Lynn_Jardin__Carol_Smith__Cheris_Hersey_3"
    If strUCI = "AIS" Then strSheet = "ChgDetail_Facility"
    Call xlOpen(strFileLoc, strUCI, strFile, strSheet, strFileType)
    'Copy existing sheets to Standard report file
     strAdhocRpt = strUCI & "_DenialReports_" & strMon & " " & iYr & strFileType
    With goXl
 [Blue]     .Workbooks(strAdhocRpt).Activate [/Blue]
      .Worksheets("DenialBalGrid_RevCode").Select
    End With
        'Copy
        sheetIndex = 29
    For Each currentSheet In Worksheets
        Windows(strAdhocRpt).Activate 'Source Workbook
        currentSheet.Select
        currentSheet.Copy After:=Workbooks(strFile).Sheets(sheetIndex) 'Target Workbook
        sheetIndex = sheetIndex + 1
    Next currentSheet
End Function

 

[vba317]

I changed some things around and I got some of the code to work but now I am getting the same error 9 a little further. The error is in blue

Call xlOpen(strFileLoc, strUCI, strFile, strSheet, strFileType)
    'Copy existing sheets to Standard report file
     strAdhocRpt = strUCI & "_DenialReports_" & strMon & " " & iYr & strFileType
    
      Windows(strAdhocRpt).Activate
      Worksheets("DenialBalGrid_RevCode").Select
           'Copy
        sheetIndex = 28
    For Each currentSheet In Worksheets
        currentSheet.Select
[Blue]        currentSheet.Copy After:=Workbooks(strFile).Sheets(sheetIndex) 'Target Workbook  [/Blue}
        sheetIndex = sheetIndex + 1
    Next currentSheet
End Function

 

[Andrzejek]

If the error happens on the BLUE line of code, my guess would be: you don't have a Workbook named - whatever you created by this line of code:
[tt]
strAdhocRpt = strUCI & "_DenialReports_" & strMon & " " & iYr & strFileType
[/tt]

Have fun.

---- Andy

 

[Andrzejek]

When the error happens, what are the values of [tt]strFile[/tt] and [tt]sheetIndex[/tt]?
Are those values VALID?

Have fun.

---- Andy

 

[vba317]

I did two copy and pastes for strFile
the first one was from the immediate window
SECURE EMAIL_AIS_Nov_2013_Lynn_Jardin__Carol_Smith__Cheris_Hersey_3.xls
SECURE EMAIL_AIS_Nov_2013_Lynn_Jardin__Carol_Smith__Cheris_Hersey_3.xls
The second one is from copy and pasting the file name
It look like they are both a match

The sheet index number is 28 which the file SECURE EMAIL_AIS_Nov_2013_Lynn_Jardin__Carol_Smith__Cheris_Hersey_3.xls has 29 tabs so that should not be a problem. I am stumped.