Results 1 to 3 of 3

macro save sheet

Threaded View

  1. #1
    Forum Contributor max_max's Avatar
    Join Date
    06-28-2013
    Location
    italy - venice
    MS-Off Ver
    Excel 2007
    Posts
    1,693

    macro save sheet

    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
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to save a single sheet from a multiple sheet workbook
    By tfilipe in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-24-2018, 02:44 PM
  2. Macro security issue - unable to save as macro enabled sheet
    By agadge123 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-31-2016, 12:36 PM
  3. Replies: 2
    Last Post: 01-27-2015, 03:16 PM
  4. [SOLVED] Auto save of data from sheet 1 to sheet 2 regularly when i run macro
    By bmbalamurali in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-17-2014, 10:24 AM
  5. Replies: 4
    Last Post: 09-03-2013, 09:56 AM
  6. [SOLVED] macro to save sheet with specific file name from sheet
    By mezza89 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-05-2013, 12:04 PM
  7. Replies: 0
    Last Post: 11-22-2012, 08:42 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1