asked on

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)

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

Norie

What is 'm_ObjWorkbook'?

Juan Velasquez

ASKER

BTW I tried ws.Copy after:=wbDestination.Worksheets(wbDestination.Worksheets.Count) and I still have the same problem

Juan Velasquez

ASKER

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

Norie

If you use m_ObjWorkbook instead of wsSource does it make a difference?

Juan Velasquez

ASKER

I just thought of that and tried it. I still get the same error.

Juan Velasquez

ASKER

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

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Juan Velasquez

ASKER

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

Juan Velasquez

ASKER

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.

Juan Velasquez

ASKER

Thanks again for taking the time to help.