Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel cannot insert the sheets - Error 1004

Posted on 2011-10-03
11
Medium Priority
?
245 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:Shanan212
  • 6
  • 5
11 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 36904396
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
 
LVL 13

Author Comment

by:Shanan212
ID: 36904469
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 36904486
Interesting. What version of Excel are you using?

Kevin
0
Independent Software Vendors: 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!

 
LVL 13

Author Comment

by:Shanan212
ID: 36904510
Office 2007 (the whole thing works on mine though) but not on another user (who uses 2007 as well)
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 36904536
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
 
LVL 13

Author Comment

by:Shanan212
ID: 36905213
I tried that and it gives the same error on this line :o

 wbSrc.Worksheets.Copy After:=wsDst
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 36905363
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
 
LVL 13

Author Comment

by:Shanan212
ID: 36905828
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
 
LVL 13

Author Comment

by:Shanan212
ID: 36905876
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
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 36905890
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
 
LVL 13

Author Closing Comment

by:Shanan212
ID: 36906232
That worked!

Thanks!
0

Featured Post

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question