0

I am trying to create a folder and save as copy of my workbook in this folder for all the filled cells in range B11-B14 of my workbook. I managed to create the folder and the file with the correct names and date, but the file is not placed in the newly created folder. It is placed in the same directory as my newly created folder. Can't figure out how to correct it. Any help would be greatly appreciated.

Sub Werkbladopslaan()

'Bestand opslaan en mappen aanmaken in @In bewerking voor alle materialen

Application.ScreenUpdating = False

Dim myName As String
Dim strDefpath As String
Dim rng As Range
Dim row As Range
Dim cell As Range

Set rng = Range("B11:B14")

For Each row In rng.Rows
  For Each cell In row.Cells
    If cell.Value > 0 Then
    
        Dim Name As String
        DateStr = Format(Date, "dd-mm-yyyy")
        Name = cell.Offset(0, 0).Text & "-" & "Leish " & DateStr
        cell.Offset(0, 3).Value = Name
        
        startPath = "N:\Sequence resultaten\@In bewerking\"
        myName = cell.Offset(0, 3).Text
        
        ActiveWorkbook.SaveCopyAs Filename:=startPath & myName & ".xlsm"
               
        Dim folderPathWithName As String
        folderPathWithName = startPath & Application.PathSeparator & myName
         
        If Dir(folderPathWithName, vbDirectory) = vbNullString Then
            MkDir folderPathWithName
        Else
           MsgBox "Map bestaat al"
           On Error Resume Next
        
        End If
              
        Worksheets("Monsterlijst").Range("D11:E14").Clear
        Worksheets("Monsterlijst").Range("C3").Select
    Else
       
    End If
  Next cell
Next row
    
Application.ScreenUpdating = True

End Sub
5
  • You create a variable call DirPath but you didn't use it.
    – rint
    Commented Mar 7 at 10:54
  • Thanks, I will remove it.
    – Caroline
    Commented Mar 7 at 12:07
  • 1
    You need to add & Application.PathSeparator & to Filename too.
    – rint
    Commented Mar 7 at 13:31
  • And make sure you create the folder before you save the file.
    – rint
    Commented Mar 7 at 13:32
  • Thank you Rint for your tips, I changed my code for saving the file to: ActiveWorkbook.SaveCopyAs Filename:=startPath & myName & Application.PathSeparator & myName & ".xlsm" and moved this line to just above "Next cell" and now it works like a charm
    – Caroline
    Commented Mar 8 at 12:45

0

You must log in to answer this question.

Browse other questions tagged .