Copy worksheet to end... copying to middle or second place instead???

[Gingertrees]

New riddle with my copy-worksheet routine:
I'm copying in a daily report from a separate workbook into my processing workbook. My brain thinks that this should put it at the end, after all the other sheets. However, this isn't happening. Why?

Sub OpenCopyApple ()
Dim tempfiletocopy As Variant
Dim tempfileName As String


Dim sheet As Worksheet


tempfiletocopy = Application.GetOpenFilename
Workbooks.Open tempfiletocopy
tempfileName = ActiveWorkbook.Name


Sheets("Sheet1").Copy After:=Workbooks("ORANGEA.xlsm").Sheets(Sheets.Count)
If Not SheetExists("apple" & Sheets.Count) Then
ActiveSheet.Name = "apple" & Sheets.Count
Else
ActiveSheet.Name = "apple" & (Sheets.Count * 3)
End If

End Sub

Function SheetExists(shName As String) As Boolean
SheetExists = False
For Each sh In ActiveWorkbook.Sheets
    If sh.Name = shName Then
        SheetExists = True
        Exit For
    End If
 Next sh
End Function

 

[Grummet]

Out of curiosity, are there any hidden sheets in this workbook?

 

[Gingertrees]

None. I have deleted a few over time, but I have saved/closed/reopened the workbook since then. Still copying the new sheet to wherever the heck it wants.

 

[Norie]

Which workbook does Sheets.Count refer to?

Is it the workbook you are copying from or the workbook you are copying to?

To clarify that try adding a workbook reference, for example to refer to the workbook the code is in use ThisWorkbook.Sheets.Count.

 

[Gingertrees]

Sheets("Sheet1").Copy After:=Workbooks("ORANGEA.xlsm").Sheets(Sheets.Count)
If Not SheetExists("apple" & Sheets.Count) Then
ActiveSheet.Name = "apple" & Sheets.Count
Else
ActiveSheet.Name = "apple" & (Sheets.Count * 3)
End If

OrangeA.xlsm is the workbook I'm copying TO; it's my processing workbook. The sheetname, which normally shows the NAME+count of sheets in OrangeA, is always correct for OrangeA. So I just don't know why it doesn't seem to get that Apple 13 should go AFTER sheet 12.

 

[Grummet]

I don't have much experience with this process as I've only had to write code for it once before. But this is a simplified version of my macro which has the user select the file they want to import the first sheet from. It then puts it at the end of all your other sheets.

It looks like you already have the other workbook opened in your process, so you may have to adjust this to your needs.

Sub CopySheet()    
    Dim wbSource As Workbook
    Dim strFilePath As String
    Dim SheetTotal As Long
    
    SheetTotal = ActiveWorkbook.Sheets.Count
    
    dialogTitle = "Select a workbook to copy from."
    Set fileDialog = Application.fileDialog(msoFileDialogFilePicker)
    With fileDialog
        .InitialFileName = ThisWorkbook.Path & "\"
        .AllowMultiSelect = False
        .Filters.Clear
        .Title = dialogTitle
        
        If .Show = False Then
            MsgBox "No file selected. Process Terminated."
            Exit Sub
        End If
        
        strFilePath = .SelectedItems(1)
        
    End With
    
    Set wbSource = Workbooks.Open(Filename:=strFilePath)
     
    wbSource.Activate
    ActiveWorkbook.Sheets(1).Copy After:=ThisWorkbook.Sheets(SheetTotal)
    wbSource.Activate
    wbSource.Close SaveChanges:=False
    
End Sub

 

[Norie]

You need to specify which workbook Sheets.Count refers to otherwise it will refer to whatever workbook is currently active, and as far as I can see that'll be the workbook you just opened.

If you are copying the worksheet from the workbook you opened to the workbook the code is in then use ThisWorkbook.Sheets.Count as I suggested.

Sheets("Sheet1").Copy After:=Workbooks("ORANGEA.xlsm").Sheets(ThisWorkbook.Sheets.Count)

 

[Gingertrees]

Norie, that worked I was not aware that I had to do that - now I know. Thanks a lot