• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

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

0
chtullu135
Asked:
chtullu135
  • 7
  • 3
1 Solution
 
NorieCommented:
What is 'm_ObjWorkbook'?
0
 
chtullu135Author Commented:
BTW I tried  ws.Copy after:=wbDestination.Worksheets(wbDestination.Worksheets.Count) and I still have the same problem
0
 
chtullu135Author 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
NorieCommented:
If you use  m_ObjWorkbook instead of wsSource does it make a difference?
0
 
chtullu135Author Commented:
I just thought of that and tried it.  I still get the same error.
0
 
chtullu135Author 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
 
NorieCommented:
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
 
chtullu135Author 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
 
chtullu135Author 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
 
chtullu135Author Commented:
Thanks again for taking the time to help.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now