• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create folder and save spreadsheets there based on criteria

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:

Code:
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
 
I figured it out.

I changed this line of code:

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

to this:

Code:
FN = "\" & WSN.Range("N2") & "\" & LastClient & " " & strDate & ".xlsx"
 
Back
Top