Runtime error 9 subscript out of range error 2

[vba317]

I am trying to copy one worksheet to the end of another workbook. The target workbook has 23 sheets in it. I am trying to write code that will copy the source worksheet to the end of the workbook. I keep on getting this error. Is there a way of designating what worksheet the new worksheet will go to because there is another project that I have to copy to that is in the middle of the workbook. The error line is highlighted in blue. Any help is appreciated.

Tom

Public Function CopyWorksheet(strUCI As String, strSourceSheetName As String, strTargetSheetName As String)
    Dim strMon As String
    Dim strFileLoc As String
    Dim strOpenStdRpt As String
    Dim strSql As String
    Dim rstMon As Recordset
    Dim strMonShort As String
    Dim strCurFY As String
    Dim strSourceWorkbook As String
    Dim strTargetWorkbook As String
    Dim strYr As String
    Dim strMonNum As String
    Dim iMon As Integer
      Call CurMonYr(strUCI, strMon, strYr, iMon)
       'Find Current Month
        strSql = "SELECT mon_txtnum,calpddiff " & _
                 "FROM dbo_dic_Period " & _
                 "WHERE calpddiff = 1;"
        Set rstMon = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)
        If Not rstMon.EOF Then
            With rstMon
                .MoveLast
                .MoveFirst
            End With
        End If
        'Assign variables for file name and sheetname
        strMonNum = rstMon![mon_txtnum]
        strFileLoc = "\\salmfilesvr1\Public\Client Services\AutoRpts\_RptSets\SALEM\HMF\" & (strYr) & "\" & (strMonNum) & "\"
        strOpenStdRpt = "SECURE EMAIL_HMF_" & (strMon) & "_" & (strYr) & "_Corcoran__Lois_98.xls"
        
        'Open destination workbook
        goXl.Workbooks.Open FileName:=strFileLoc & strOpenStdRpt
        'Assign Variables for Current workbook name
        strSourceWorkbook = "HMF_CPT95951_" & (strMon) & (strYr) & ".xls"
        strTargetWorkbook = strOpenStdRpt
         'Copy to new sheet
[Blue]        goXl.Sheets(strSourceWorkbook).Copy After:=Workbooks(strTargetWorkbook).Sheets(Workbooks(strTargetWorkbook).Sheets.Count) [/Blue]
       
        'Close
        goXl.ActiveWorkbook.Close
End Function

 

[SkipVought]

Hi,

Excel reference object on ALL Excel objects!!!

        goXl.Sheets(strSourceWorkbook).Copy After:=goal.Workbooks(strTargetWorkbook).Sheets(goal.Workbooks(strTargetWorkbook).Sheets.Count)

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[vba317]

Skip,

I tried and I still got the same error.

 goXl.Sheets(strSourceWorkbook).Copy After:=goXl.Workbooks(strTargetWorkbook).Sheets(goXl.Workbooks(strTargetWorkbook).Sheets.Count)

 

[PHV]

strSourceWorkbook is an xls filename, not a sheetname !

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

PHV,
I have corrected the wrong name and the code works for this project. Which is to paste the sheet to the end of the worksheet. My next project needs to paste the code in between two sheets. Chgdet_Prv is the name of the page I want to paste the new sheet after. I have tried to change the Last piece of code .Sheets.Count to .Sheets(23) or .Worksheets(23) or .sheets("Chgdet_Prv") or .worksheets("Chgdet_Prv") these solutions did not work. Do you have any suggestions?

 

[vba317]

Using goXl.Sheets(strSourceWorksheet).Copy After:=goXl.Workbooks(strTargetWorkbook).Sheets(goXl.Workbooks(strTargetWorkbook).Worksheets(23)) I get an Runtime error 13 type mismatch.

 

[PHV]

And this ?
goXl.Sheets(strSourceWorksheet).Copy After:=goXl.Workbooks(strTargetWorkbook).Sheets(goXl.Workbooks(strTargetWorkbook).Sheets.Count)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

PHV,
The code you just posted works to paste the sheet from the source workbook to the last sheet of the target workbook. What do I need to change to have the source sheet paste after a specific sheet?

 

[SkipVought]

MySpecificSheet = "My Sheet"
goXl.Sheets(strSourceWorksheet).Copy After:=goXl.Workbooks(strTargetWorkbook).Sheets(MySpecificSheet)

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[PHV]

Like this ?
goXl.Sheets(strSourceWorksheet).Copy After:=goXl.Workbooks(strTargetWorkbook).Sheets("Chgdet_Prv")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

PHV,
I get a run-time error 9 subscript out of range.

 

[PHV]

Double check the spelling of Chgdet_Prv

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

I double checked the spelling and it is correct. I did a rename of the sheetname copied and pasted the name into the code to verify.

 

[SkipVought]

'subscript out of range' is almost always an invalid index/name to an object collection.

in this case...

goXl.Sheets(strSourceWorksheet).Copy After:=goXl.Workbooks(strTargetWorkbook).Sheets("Chgdet_Prv")

I'd be suspect of 1) no WORKBOOK object for the source sheet, 2) the values of strSourceWorksheet & strTargetWorkbook

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[vba317]

I changed the variables and added the Workbooks in the beginning. Now it works. Thanks everyone for sticking in there with me.

Tom

 goXl.Workbooks(strS_WrkBk).Sheets(strS_ShtNm).Copy After:=Workbooks(strT_WrkBk).Sheets(Workbooks(strT_WrkBk).Sheets.Count)

 

[PHV]

To avoid ghosts excel.exe:
goXl.Workbooks(strS_WrkBk).Sheets(strS_ShtNm).Copy After:=[!]goXl.[/!]Workbooks(strT_WrkBk).Sheets([!]goXl.[/!]Workbooks(strT_WrkBk).Sheets.Count)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886