Link to home
Start Free TrialLog in
Avatar of Shanan212
Shanan212Flag for Canada

asked on

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  User generated image(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
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

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
Avatar of Shanan212

ASKER

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

Interesting. What version of Excel are you using?

Kevin
Office 2007 (the whole thing works on mine though) but not on another user (who uses 2007 as well)
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
I tried that and it gives the same error on this line :o

 wbSrc.Worksheets.Copy After:=wsDst
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
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 :/
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)
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That worked!

Thanks!