Hi to all.
This macro save sheet in new directory
I don't understand why if the value of cell L2 (Foglio1) is a name it works if it's a date it doesn't work
Option Explicit
Sub EsportaFoglio()
Dim NomeFoglio, CurFolder, DestFolder, Destfile
Dim Shp As Shape
Dim name1, name2, name As String
Dim nSfx As Long
Dim est As String
Dim sUt As String
Dim sData As String
On Error GoTo gest_err
name1 = Foglio2.Range("I2").Value
name2 = Foglio2.Range("L2").Value
NomeFoglio = ActiveSheet.name
CurFolder = ActiveWorkbook.Path
sData = Format(Date, "dd.mm.yyyy") 'data del file salvato
sUt = Application.UserName
DestFolder = CurFolder & "\" & name1 & "\"
If Dir(DestFolder, vbDirectory) = "" Then MkDir DestFolder
'--------------------------------------------------------------------------------------
'loop per creazione nome file
Do
nSfx = nSfx + 1
'--------------------------------------------------------------------------------------
'estensione salvataggio
'est = ".xls" ' oppure xlsx
est = ".xlsx" ' oppure xls
'Destfile = DestFolder & NomeFoglio & " - " & sData & " - " & nSfx & est
Destfile = DestFolder & NomeFoglio & " - " & name2 & " - " & nSfx & est
'--------------------------------------------------------------------------------------
'loop per creazione nome file progressivo
Loop While Dir(Destfile) <> vbNullString
'--------------------------------------------------------------------------------------
'--------------------------------------------------------------------------------------
Application.ScreenUpdating = False
Sheets(NomeFoglio).Select
Sheets(NomeFoglio).Copy
'--------------------------------------------------------------------------------------
'---------------------------------------------------------------------------------------
ActiveWorkbook.SaveAs Filename:=Destfile, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close 'se non attivo mostra il nuovo file
gest_err:
If Err.Number <> 0 Then
MsgBox "Errore " & Err.Number & ": " & Err.Description, vbCritical, "Errore"
End If
Application.ScreenUpdating = True
End Sub
Bookmarks