What could be the problem here ? Getting a Runtime error “1004”

Hi I am trying to run this macro

Sub SaveWorkBookRangeAs()
Dim i As Integer
Dim sPath As String
Dim sFileName As String
Dim LastRow As Integer
LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
sPath = ActiveWorkbook.Path & "\"
For i = 2 To LastRow
Sheets("Sheet1").Range("A1: M" & LastRow).Copy
sFileName = Range("N" & i).Value & ".xlsx"
Workbooks.Add
ActiveSheet.Paste Destination:=Range("A1")
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs fileName:=sPath & sFileName
Application.DisplayAlerts = True
Next i
End Sub

I am getting Run-Time error ‘1004’

Method ‘SaveAs’ of Object_workbook’ failed

 

what have I done wrong?

 

Regards

 

Raghu

Expert Asked on August 18, 2018 in VBA: General.
Add Comment
1 Answer(s)

I believe the problem is that you are not getting the sFileName from the original workbook.  So I have added ActiveWorkbook.Save and ActiveWorkbook.Close statements to your code.  Please try this!

Sub SaveWorkBookRangeAs()
Dim i As Integer
Dim sPath As String
Dim sFileName As String
Dim LastRow As Integer
LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
sPath = ActiveWorkbook.Path & "\"
For i = 2 To LastRow
Sheets("Sheet1").Range("A1: M" & LastRow).Copy
sFileName = Range("N" & i).Value & ".xlsx"
Workbooks.Add
ActiveSheet.Paste Destination:=Range("A1")
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=sPath & sFileName
Application.DisplayAlerts = False
ActiveWorkbook.Save
ActiveWorkbook.Close
Next i
End Sub
Expert Answered on August 18, 2018.

Thanks, Hayside2,

 

I  was trying to resolve this. Saw many videos and nothing helped. One even suggested that I uninstall Microsoft Office and reinstall it again.

 

Your solution is brilliant! Thanks once again.

on August 18, 2018.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.