Create folder and save spreadsheets there based on criteria

[msquared99]

I have a spreadsheet with data in columns A:N, headers are in row 1. Currently my macro takes the client name (there are 24 clients) in column N and creates a spreadsheet and saves it to a folder. In column N there are the names of the vendors associated with the clients. What I would like to do is have the macro create a sub-folder for each vendor (4 different vendors) and save the spreadsheet of the client in the vendor specific folder.

What would be the best way of doing this?

The code that saves the spreadsheet is below:

LastClient = Cells(2, 13)
  startrow = 2
 
  For r = 2 To finalrow
  ThisClient = WSO.Cells(r, 13)
  If ThisClient = LastClient Then
 
  Else
  lastrow = r - 1
  RowCount = lastrow - startrow + 1
 
  Set WBN = Workbooks.Add(template:=xlWBATWorksheet)
  Set WSN = WBN.Worksheets(1)
 
  WSN.Name = "Sheet1"
 
  WSO.Range("A1:M1").Copy Destination:=WSN.Cells(1, 1)
 
  WSO.Range(WSO.Cells(startrow, 1), WSO.Cells(lastrow, 13)).Copy Destination:=WSN.Cells(2, 1)
 
  FN = LastClient & " " & strDate & ".xlsx"
  FP = WBO.Path & Application.PathSeparator
  WBN.SaveAs Filename:=FP & FN
  WBN.Close savechanges:=False
 
  LastClient = ThisClient
  startrow = r
  End If
  Next r

 

[msquared99]

I figured it out.

I changed this line of code:

FN = LastClient & " " & strDate & ".xlsx"

to this:

FN = "\" & WSN.Range("N2") & "\" & LastClient & " " & strDate & ".xlsx"