Shanan212
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
Error on: wbSrc.Worksheets.Copy After:=wsDst
Error (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
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)
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
Interesting. What version of Excel are you using?
Kevin
Kevin
ASKER
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
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
ASKER
I tried that and it gives the same error on this line :o
wbSrc.Worksheets.Copy After:=wsDst
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.DefaultSaveFor mat
Application.DefaultSaveFor mat = xlOpenXMLWorkbook
wbkNew.SaveAs Filename:=fPath & "Payroll GL Summary.xlsx"
Application.DefaultSaveFor mat = DefaultFileFormat
Application.DisplayAlerts = True
End Function
Kevin
Function CreateNew()
Dim wkb As Workbook, wbkNew As Workbook
Dim fPath As String
Dim DefaultFileFormat As XlFileFormat
Application.ScreenUpdating
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.DefaultSaveFor
Application.DefaultSaveFor
wbkNew.SaveAs Filename:=fPath & "Payroll GL Summary.xlsx"
Application.DefaultSaveFor
Application.DisplayAlerts = True
End Function
Kevin
ASKER
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 :/
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 :/
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That worked!
Thanks!
Thanks!
Kevin