Excel application error when trying to copy worksheet

Hello,

I have a destination workbook which contains to worksheets.  I am trying to copy a worksheet from another workbook into this destination workbook but keep getting the following error message on line  
ws.Copy before:=wbDestination.Worksheets(wbDestination.Worksheets.Count)


Error Message
Public Sub CopyWorksheets()
    ' Comments:
    ' Params  :
    ' Created : 07/16/12 08:38 JV
    ' Modified:
    
    On Error GoTo PROC_ERR
    
    Dim wbDestination As Workbook
    Dim wbSource As Workbook
    Dim ws As Worksheet
    
    Set wbDestination = ThisWorkbook
    
    Set wbSource = m_ObjWorkbook
    gblIsInterestDownloadResident = fnIsInterestDownloadResident
    
    If Left(wbSource.Name, 2) <> "GL" Then
        
        For Each ws In wbSource.Worksheets
            If Left(ws.Name, 2) <> "sh" Then
                ws.Copy before:=wbDestination.Worksheets(wbDestination.Worksheets.Count)
            End If
        Next ws
        wbSource.Close SaveChanges:=False
        If gblIsInterestDownloadResident = False Then
            Call CopyInterestWorksheet
        Else
            gblReadyToGo = True
            Call PopulateAmortizationSchedules
        End If
    End If
    
PROC_EXIT:
    Exit Sub
    
PROC_ERR:
    MsgBox Err.Number & Err.Description, vbCritical, "LoopThroughWorksheetsInWorkbook.CopyWorksheets"
    Resume PROC_EXIT
    
End Sub

Open in new window

Juan VelasquezAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
Is there anything in this particular sourcebook that's different?

Does it happen only on one worksheet in this workbook or all of them?
0
 
NorieVBA ExpertCommented:
What is 'm_ObjWorkbook'?
0
 
Juan VelasquezAuthor Commented:
BTW I tried  ws.Copy after:=wbDestination.Worksheets(wbDestination.Worksheets.Count) and I still have the same problem
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Juan VelasquezAuthor Commented:
m_objWorkbook is a module level variable set in the calling procedure.  Below is the calling code

Public Sub OpenWorkbook(strFileName As String, fReadOnly As Boolean, Optional strPassword As String)
    ' Comments: Opens the named file and associates it with the class
    ' Params  : strFileName     Full path and name of the file to open
    '           fReadOnly       True to open readonly
    '           strPassword     Optional: specify the password if the workbook file is password protected.
    ' Source  : Total Visual SourceBook
    
    On Error GoTo PROC_ERR
    
    If Not IsMissing(strPassword) Then
        Set m_ObjWorkbook = Workbooks.Open(strFileName, , fReadOnly, , strPassword)
    Else
        Set m_ObjWorkbook = Workbooks.Open(strFileName, , fReadOnly)
    End If
    
    Call CopyWorksheets
    
PROC_EXIT:
    Exit Sub
    
PROC_ERR:
    MsgBox "Error: " & Err.Number & ". " & Err.Description, , "CExcel.OpenWorkbook"
    Resume PROC_EXIT
End Sub

Open in new window

0
 
NorieVBA ExpertCommented:
If you use  m_ObjWorkbook instead of wsSource does it make a difference?
0
 
Juan VelasquezAuthor Commented:
I just thought of that and tried it.  I still get the same error.
0
 
Juan VelasquezAuthor Commented:
Okay, the problem appears to be caused by the source workbooks.  Other source workbooks seem to work fine.  What in the source workbook would cause the above symptom
0
 
Juan VelasquezAuthor Commented:
It seemed to happen on all the worksheets for that workbook .  When I get to work tomorrow, I will try exporting the worksheets to new workbook.  I will then run the code against that new workbook.  There may be something hidden in the affected workbooks
0
 
Juan VelasquezAuthor Commented:
My apologies,
I had thought that I had closed this quesiton.  I must have forgotten to hit the submit key.  I ended up exporting the worksheets to a new workbook and running the code against that new workbook.  Everything worked fine.  There must have been something either hidden in the old workbook or some sort of corruption.
0
 
Juan VelasquezAuthor Commented:
Thanks again for taking the time to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.