Saving Workbook VBA

[ziad alsayed]

dear all

i am facing a problem with the below code when reaching the line of saving the workbook . Appreciate if you assist in sorting out the problem

Sub CreateWorkbooks()
    Dim WBO As Workbook ' original workbook
    Dim WBN As Workbook ' new workbook
    Dim WSO As Worksheet ' original worksheet
    Dim WSN As Worksheet ' new worksheet
    
    Set WBO = ActiveWorkbook
    Set WSO = ActiveSheet
    
    ' filter to get all the None
    Range("B1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$F$500000").AutoFilter Field:=2, Criteria1:="None"
'find finalrow
    finalrow = WSO.Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    LastBranch = Cells(2, 3)
    StartRow = 2
    
    For i = 2 To finalrow
        ThisBranch = WSO.Cells(i, 3)
        If ThisBranch = LastBranch Then
            ' do nothing
        Else
            ' We have a new branch
            ' Copy all of the previous rows to a new workbook
            LastRow = i - 1
            RowCount = LastRow - StartRow + 1
            
            ' Create a new workbook.
            Set WBN = Workbooks.Add(Template:=xlWBATWorksheet)
            Set WSN = WBN.Worksheets(1)
            
            ' Set up the headings for the report
            WSN.Cells(1, 1).Value = "Code"
            WSN.Cells(1, 2).Value = "Profile"
            WSN.Cells(1, 3).Value = "Branch"
            WSN.Cells(1, 4).Value = "Description"
            
            ' copy all of the records for this branch
           WSO.Range(WSO.Cells(StartRow, 1), WSO.Cells(LastRow, 4)).Copy Destination:=WSN.Cells(2, 1)
           [COLOR=red] ' save WBN
            ' All WBN should be Saved In G:\Nigeria Files\Top ten\No Profile
            FN = LastBranch & ".xls"
            FP = WBO.Path & Application.PathSeparator & "No Profile" & Application.PathSeparator
            
            WBN.SaveAs Filename:=FP & FN
[/COLOR]            WBN.Close SaveChanges:=False
            
            LastBranch = ThisBranch
            StartRow = i
        End If
    Next i
End Sub

 

[Tom Urtis]

At first glance, you did not declare the FN or FP variables, so if you have the Option Explicit statement at the top of your module, as you and the entire Excel-using world should, your error (though you did not say exactly what error it is so we are in the dark about that) would be caused by that.

Try declaring them both as Strings and see if that fixes anything.

 

[ziad alsayed]

dear Tom

thanks for your imput, i just closed the workbook and re open ( By Hazard) and the macro worked perfectly.

thanks again.