Excel cannot insert the sheets - Error 1004

Sub GetThemAll()
Dim wbDst As Workbook, wbSrc As Workbook
Dim wsDst As Worksheet, ws As Worksheet
Dim strPath As String, af As AutoFilter
Dim strFileName As String
  
    strPath = "C:\GL Summary\"    ' change path as required
  
    Set wbDst = Workbooks("Payroll GL Summary.xlsx")
    
    strFileName = Dir(strPath & "*.xlsx")
  
    Application.ScreenUpdating = False
  
    While strFileName <> ""
  
        Set wsDst = wbDst.Worksheets(wbDst.Worksheets.Count)
  
        Set wbSrc = Workbooks.Open(strPath & strFileName)
  
        Set ws = ActiveWorkbook.ActiveSheet
        Set af = ws.AutoFilter
        If Not af Is Nothing Then
            If af.FilterMode Then
                af.ShowAllData ' clear filters
            End If
        End If

        wbSrc.Worksheets.Copy After:=wsDst
               
        wbSrc.Close False
  
        strFileName = Dir
    Wend
  
    Application.ScreenUpdating = True
  
End Sub

Open in new window


Error on: wbSrc.Worksheets.Copy After:=wsDst

Error  screen(see picture)

Hi,

I am getting the above error. I really have no idea as it works (the function) and no on another computer (same settings) The files this function 'copy' and 'paste' from are also same.

I have no idea why it would give this error on another computer while EVERYTHING is same (unless I am missing something)

Could you please help me debug?

Thanks!
Shanan
LVL 13
Shanan212Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
The target workbook is an earlier format workbook. Open and save the target workbook in Excel 2007 or 2010 with the XLSX format and your code should work.

Kevin
0
Shanan212Author Commented:
When you say target, I am assuming to which the files are written into? aka the destination file in above function?

This is how I am creating it and it is created as 'xlsx' ?! (kinda confused here)

Function CreateNew()
    Dim wkb As Workbook, wbkNew As Workbook
    Dim fPath As String
      
    Application.ScreenUpdating = True
      
    fPath = "C:\Documents and Settings\All Users\Desktop\"
    On Error Resume Next
    Set wkb = Workbooks("Payroll GL Summary.xlsx")
    If Err.Number = 0 Then 'workbook is open
        Application.EnableEvents = False 'just in case we trigger a beforeClose event we don't want
        wkb.Close savechanges:=False 'so close it, without saving changes
        Application.EnableEvents = True
    End If
    
    On Error GoTo 0
      
    Set wbkNew = Workbooks.Add
      
    Application.DisplayAlerts = False 'to avoid being prompted on file overwrite
    wbkNew.SaveAs Filename:=fPath & "Payroll GL Summary.xlsx"
    Application.DisplayAlerts = True
      
End Function

Open in new window

0
zorvek (Kevin Jones)ConsultantCommented:
Interesting. What version of Excel are you using?

Kevin
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Shanan212Author Commented:
Office 2007 (the whole thing works on mine though) but not on another user (who uses 2007 as well)
0
zorvek (Kevin Jones)ConsultantCommented:
The default file format on the other user's installation may be set to the earlier format.

Replace this:

    wbkNew.SaveAs Filename:=fPath & "Payroll GL Summary.xlsx"

with:

    wbkNew.SaveAs Filename:=fPath & "Payroll GL Summary.xlsx", FileFormat:= xlOpenXMLWorkbook

This will force the new workbook to be in the new format.

Kevin
0
Shanan212Author Commented:
I tried that and it gives the same error on this line :o

 wbSrc.Worksheets.Copy After:=wsDst
0
zorvek (Kevin Jones)ConsultantCommented:
Try this version of the CreateNew routine:

Function CreateNew()
    Dim wkb As Workbook, wbkNew As Workbook
    Dim fPath As String
    Dim DefaultFileFormat As XlFileFormat
     
    Application.ScreenUpdating = True
     
    fPath = "C:\Documents and Settings\All Users\Desktop\"
    On Error Resume Next
    Set wkb = Workbooks("Payroll GL Summary.xlsx")
    If Err.Number = 0 Then 'workbook is open
        Application.EnableEvents = False 'just in case we trigger a beforeClose event we don't want
        wkb.Close savechanges:=False 'so close it, without saving changes
        Application.EnableEvents = True
    End If
   
    On Error GoTo 0
   
    Set wbkNew = Workbooks.Add
     
    Application.DisplayAlerts = False 'to avoid being prompted on file overwrite
    DefaultFileFormat = Application.DefaultSaveFormat
    Application.DefaultSaveFormat = xlOpenXMLWorkbook
    wbkNew.SaveAs Filename:=fPath & "Payroll GL Summary.xlsx"
    Application.DefaultSaveFormat = DefaultFileFormat
    Application.DisplayAlerts = True
     
End Function

Kevin
0
Shanan212Author Commented:
Nope, same error!

On this 'other computer' I am talking about - It opens the first file (that it copies that worksheet tab) and then crashes and gives the error - I am just guessing that this may not be a 'file-type' error but hey! i m not an expert so :/
0
Shanan212Author Commented:
Ow maybe you guys are looking at it as the '64,000' rows limit vs '1M' rows limit? I dont have that many lines btw! (just thinking loud/writing)
0
zorvek (Kevin Jones)ConsultantCommented:
Dang! I did the above code incorrectly. Try this version:

Function CreateNew()
    Dim wkb As Workbook, wbkNew As Workbook
    Dim fPath As String
    Dim DefaultFileFormat As XlFileFormat
     
    Application.ScreenUpdating = True
     
    fPath = "C:\Documents and Settings\All Users\Desktop\"
    On Error Resume Next
    Set wkb = Workbooks("Payroll GL Summary.xlsx")
    If Err.Number = 0 Then 'workbook is open
        Application.EnableEvents = False 'just in case we trigger a beforeClose event we don't want
        wkb.Close savechanges:=False 'so close it, without saving changes
        Application.EnableEvents = True
    End If
   
    On Error GoTo 0
   
    DefaultFileFormat = Application.DefaultSaveFormat
    Application.DefaultSaveFormat = xlOpenXMLWorkbook
    Set wbkNew = Workbooks.Add
    Application.DefaultSaveFormat = DefaultFileFormat
   
    Application.DisplayAlerts = False 'to avoid being prompted on file overwrite
    wbkNew.SaveAs Filename:=fPath & "Payroll GL Summary.xlsx"
    Application.DisplayAlerts = True
     
End Function

And, yes, it is about the rows and columns in the target workbook.

Kevin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Shanan212Author Commented:
That worked!

Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

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.