Save a file

[vmjan02]

I have this code it creates a folder and code is trying to save the current file in the created folder

but not sure its not working, can there be a fix.

Sub FoldtoBackup()

'===================================================
    ' This will creat a backup folder by name Daily Hajj Final Report
'===================================================

Dim Path As String
Dim path1 As String
Dim Folder As String
Dim Answer As VbMsgBoxResult
Dim relativePath As String
    Path = Application.ThisWorkbook.Path & "\" & "Daily Hajj Final Report"
    Folder = Dir(Path, vbDirectory)
    If Folder = vbNullString Then
                 VBA.FileSystem.MkDir (Path)
    'Exit Sub
    Else
        'MsgBox "Folder exists."
    End If
 
 
Sheets(Array("Summary", "Sheet1", "Fiber RawData", _
        "Copper RawData", "FTTM 1 RawData", "FTTM 2 RawData", "FTTH RawData", "Fiber Mak Mad Haram")).Select
    Sheets("Summary").Activate
    Sheets(Array("Summary", "Sheet1", "Fiber RawData", _
        "Copper RawData", "FTTM 1 RawData", "FTTM 2 RawData", "FTTH RawData", "Fiber Mak Mad Haram")).Copy
    ChDir Path           '"D:\"
    
    On Error Resume Next
    'Kill "D:\Print Raw Data.xlsx"
    ActiveWorkbook.SaveAs Filename:= _
        Path & "\" & Sheets("Report").Range("d5").Value.xlsx, FileFormat _
        :=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close  ''' Here is the problem
    Sheets("Report").Select
    Range("E7").Select
 
 
End Sub

 

[davesexcel]

You don't have Sheets("Report").Range("d5") in the activeworkbook

 

[Herakles]

I have this code it creates a folder and code is trying to save the current file in the created folder

but not sure its not working, can there be a fix.

Sub FoldtoBackup()

'===================================================
    ' This will creat a backup folder by name Daily Hajj Final Report
'===================================================

Dim Path As String
Dim path1 As String
Dim Folder As String
Dim Answer As VbMsgBoxResult
Dim relativePath As String
    Path = Application.ThisWorkbook.Path & "\" & "Daily Hajj Final Report"
    Folder = Dir(Path, vbDirectory)
    If Folder = vbNullString Then
                 VBA.FileSystem.MkDir (Path)
    'Exit Sub
    Else
        'MsgBox "Folder exists."
    End If
 
 
Sheets(Array("Summary", "Sheet1", "Fiber RawData", _
        "Copper RawData", "FTTM 1 RawData", "FTTM 2 RawData", "FTTH RawData", "Fiber Mak Mad Haram")).Select
    Sheets("Summary").Activate
    Sheets(Array("Summary", "Sheet1", "Fiber RawData", _
        "Copper RawData", "FTTM 1 RawData", "FTTM 2 RawData", "FTTH RawData", "Fiber Mak Mad Haram")).Copy
    ChDir Path           '"D:\"
   
    On Error Resume Next
    'Kill "D:\Print Raw Data.xlsx"
    ActiveWorkbook.SaveAs Filename:= _
        Path & "\" & Sheets("Report").Range("d5").Value.xlsx, FileFormat _
        :=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close  ''' Here is the problem
    Sheets("Report").Select
    Range("E7").Select
 
 
End Sub

Are you trying to save all of the sheets in the workbook?

 

[rlv01]

This:

    ActiveWorkbook.SaveAs Filename:= _
        Path & "\" & Sheets("Report").Range("d5").Value.xlsx, FileFormat _
        :=xlOpenXMLWorkbook, CreateBackup:=False

probably needs to be this:

    ActiveWorkbook.SaveAs Filename:= _
        Path & "\" & Sheets("Report").Range("d5").Value & ".xlsx", FileFormat _
        :=xlOpenXMLWorkbook, CreateBackup:=False

Note: If you had not turned off error checking with this statement: On Error Resume Next, it would probably be a lot more obvious. Error checking is your friend.

 

[vmjan02]

Are you trying to save all of the sheets in the workbook?

No only specific sheets (mention on my earlier code

 

[Herakles]

Which of these lines is causing the error?

ActiveWindow.Close  ''' Here is the problem
    Sheets("Report").Select
    Range("E7").Select

 

[vmjan02]

Path & "\" & Sheets("Report").Range("d5").Value & ".xlsx"

It is giving me this error as Run-time error '-214735....'
The specified dimension is not valid for the current chart type.

 'On Error Resume Next
    
    ActiveWorkbook.SaveAs Filename:= _
        Path & "\" & Sheets("Report").Range("d5").Value & ".xlsx", FileFormat _
        :=xlOpenXMLWorkbook, CreateBackup:=False

 

[vmjan02]

Which of these lines is causing the error?

ActiveWindow.Close  ''' Here is the problem
    Sheets("Report").Select
    Range("E7").Select

This part

 'On Error Resume Next
    
    ActiveWorkbook.SaveAs Filename:= _
        Path & "\" & Sheets("Report").Range("d5").Value & ".xlsx", FileFormat _
        :=xlOpenXMLWorkbook, CreateBackup:=False

 

[Herakles]

The Path property in Excel VBA returns the complete, saved path to a workbook.

Do not use Path as a variable name although this was not the problem.

Try this.

Sub FoldtoBackup()
Dim strPath As String
Dim path1 As String
Dim Folder As String
Dim Answer As VbMsgBoxResult
Dim relativePath As String
Dim strFileName As String

    strPath = Application.ThisWorkbook.Path & "\" & "Daily Hajj Final Report"
    
    Folder = Dir(strPath, vbDirectory)
    
    If Folder = vbNullString Then
        VBA.FileSystem.MkDir (strPath)
    Else
        MsgBox "Folder exists."
    End If

    strFileName = strPath & "\" & Sheets("Report").Range("D5").Value & ".xlsx"
    
    Worksheets(Array("Summary", "Sheet1", "Fiber RawData", _
        "Copper RawData", "FTTM 1 RawData", "FTTM 2 RawData", "FTTH RawData", "Fiber Mak Mad Haram")).Copy
    
    With ActiveWorkbook
        .SaveAs Filename:=strFileName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        .Close
    End With
    
    With ActiveWorkbook
        .Worksheets("Report").Activate
        ActiveSheet.Range("E7").Select
    End With
      
End Sub

 

[davesexcel]

Refer to post #2