SALVAR ABRINDO A CAIXA DE DIALOGO

Oii, Andre! Tudo bem?

Neste caso, pode utilizar o Application.GetSaveAsFilename. Essa função abre uma caixa de diálogo que permite ao usuário selecionar o local e o nome do arquivo.

Segue um exemplo do código que pode ser usado:

Sub SalvarCSV()
    Dim caminhoArquivo As Variant
    
    ' Abre a caixa de diálogo para salvar o arquivo
    caminhoArquivo = Application.GetSaveAsFilename( _
        InitialFileName:="Vendas - com formato - " & Format(Now(), "yyyy-mm-dd-HH-MM-ss") & ".xlsx", _
        FileFilter:="Excel Workbook (*.xlsx), *.xlsx", _
        Title:="Salvar Arquivo Como")
    
    ' Verifica se o usuário cancelou a caixa de diálogo
    If caminhoArquivo <> False Then
        ' Salva o arquivo no caminho escolhido
        ActiveWorkbook.SaveAs Filename:=caminhoArquivo, _
            FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    End If
End Sub

O exemplo acima, a caixa de diálogo será aberta com um nome de arquivo sugerido que inclui a data e hora atuais, como você já estava fazendo. O usuário poderá então escolher onde salvar o arquivo e, se clicar em "Salvar", o arquivo será salvo no local especificado. E se precisar ajustar o código para se encaixar ao seu projeto, fique à vontade.

Abaixo deixo um gif ilustrando como ficará a ação do código ao salvar:

Espero ter sanado a sua dúvida, conte sempre conosco para te auxiliar.

Bons estudos, André!