Runtime error 1004 when save as

[studentlearner]

So the function is working as how I intended, just that after it finishes saving as it keeps the last workbook opened and the last created worksheet still opened. how do I prevent the error from popping up?

Sub CreateBranchSheets()

Dim BranchField As Range
Dim BranchName As Range
Dim NewWSheet As Worksheet
Dim WSheet As Worksheet
Dim WSheetFound As Boolean
Dim DataWSheet As Worksheet

Set DataWSheet = Worksheets("Schedule")
Set BranchField = DataWSheet.Range("A2", DataWSheet.Range("A2").End(xlDown))

Application.ScreenUpdating = False

'Loop through each branch name in column D

For Each BranchName In BranchField




'Check whether the current branch name corresponds with an existing sheet name

For Each WSheet In ThisWorkbook.Worksheets
If WSheet.Name = BranchName Then
WSheetFound = True
Exit For ' if it does assign True to the WSheetFound variable and exit the For Each Next Loop
Else
WSheetFound = False ' if it doesn't assign False to the WSheetFound variable
End If
Next WSheet


If WSheetFound Then 'if WSheetFound = True

'copy and paste the record to the relevant worksheet, in the next available row



Else 'if WSheetFound = False

Sheets("Rubrics").Copy After:=Sheets(9)
ActiveSheet.Name = BranchName
ActiveSheet.Range("D1").Value = "Project ID: " & BranchName
ActiveSheet.Range("D2").Value = "Project Title: " & BranchName.Offset(1, 3)

Application.DisplayAlerts = False
ActiveSheet.Copy
With ActiveWorkbook
.SaveAs Filename:="C:\Users\john\Desktop\Project\" & BranchName.Offset(1, 2) & "\" & BranchName & "\" & BranchName & ".xlsx", FileFormat:=51
.Close False
End With


ActiveSheet.Delete
Application.DisplayAlerts = True







End If


Next BranchName

'autofit columns in each sheet in the workbook

For Each WSheet In ThisWorkbook.Worksheets

WSheet.UsedRange.Columns.AutoFit

Next WSheet

Application.ScreenUpdating = True







End Sub

 

[Joe4]

If you hit "Debug" when the error message pops up, which line of code does it highlight (this is the problem row)?
Is it this one?

.SaveAs Filename:="C:\Users\john\Desktop\Project\" & BranchName.Offset(1, 2) & "\" & BranchName & "\" & BranchName & ".xlsx", FileFormat:=51

If so, let's say what it is trying to build before saving it by replacing that code with this, which will return a message box with the full file path and name, so we can see if there are any issues (i.e. using illegal characters for file paths or file names).

Dim fname as String
fname = "C:\Users\john\Desktop\Project\" & BranchName.Offset(1, 2) & "\" & BranchName & "\" & BranchName & ".xlsx"
MsgBox fname
.SaveAs Filename:=fname, FileFormat:=51

If it is not obvious to you what the problem is after seeing that, pleast post exactly what the message box returns.

 

[studentlearner]

There's no debugging whatsoever, it only displays run-time error '1004' with 'Ok' and 'Help' button, just that the last of the function of sheets and workbook doesn't delete and closes itself and thereby popping up the error window

 

[studentlearner]

Hi, here I tried using the MsgBox and apparently it doesn't read the branchName.Offset and displayed this:

C:\Users\john\Desktop\Project\\877452\877452.xlsx

Highllighted is the problem code:
fname = "C:\Users\john\Desktop\Project\" & BranchName.Offset(1, 2) & "\" & BranchName & "\" & BranchName & ".xlsx"

 

[Alex Blakenburg]

Now that you got that far add this msgbox after Joe's and see if the results make sense and if you are not sure send us a picture

    MsgBox "Branch Name: " & BranchName.Value & vbLf & _
            "Branch Cell: " & BranchName.Address & vbLf & _
            "Branch Cells Sheet: " & BranchName.Parent.Name & vbLf & _
            "Branch Offset cell: " & BranchName.Offset(1, 2).Address & vbLf & _
            "Branch Offset value: " & BranchName.Offset(1, 2).Value & vbLf & _
            "Branch Offset Sheet: " & BranchName.Offset(1, 2).Parent.Name