Fastest way to put files in a network folder / saveas or filecopy?

[ziv26]

I want to save Excel files in both the local drive and in the network folder. Currently I am doing it with SaveAs (local) and another SaveAs (network), is it faster to do a SaveAs then FileCopy?

Code below:

Sub SaveAs()

    Dim ws As Worksheet
    Dim ws_console As Worksheet
    Dim long_col_number As Long
    Dim long_sheets_count As Long
    Dim arr_sheet_names As Variant
    Dim str_password As String
    Dim str_datetoday As String
    Dim str_datetoday_path As String
    Dim str_datetoday_network_path As String

    str_datetoday = Format(Date, "yyyy-mm-dd")
    str_datetoday_path = "C:\Users\" & Environ("Username") & "\Desktop\Report\" & str_datetoday
    str_datetoday_network_path = "\\servername\data\reports\US Reports Daily\" & str_datetoday

    If Dir(str_datetoday_path, vbDirectory) = "" Then
        MkDir (str_datetoday_path)
        MsgBox "Making directory"
    End If

    If Dir(str_datetoday_network_path, vbDirectory) = "" Then
        MkDir (str_datetoday_network_path)
    End If

    For Each ws In ThisWorkbook.Worksheets
        If ws.CodeName = "AILD_01_Console" Then
            Set ws_console = ws
            Exit For
        End If
    Next ws

    long_col_number = 0

    For long_col_number = 1 To 8

        long_sheets_count = Application.WorksheetFunction.CountA(ws_console.Range(Cells(16, long_col_number), Cells(24, long_col_number)))

        arr_sheet_names = ws_console.Range(Cells(16, long_col_number), Cells(15 + long_sheets_count, long_col_number))
        arr_sheet_names = Application.WorksheetFunction.Transpose(arr_sheet_names)

        Worksheets(arr_sheet_names).Copy
        ActiveWorkbook.SaveAs _
            Filename:=str_datetoday_path & "\" & ws_console.Cells(15, long_col_number) & " - " & Format(Date, "yyyy-mm-dd"), _
            FileFormat:=51

        ActiveWorkbook.SaveAs _
            Filename:=str_datetoday_network_path & "\" & ws_console.Cells(15, long_col_number), _
            FileFormat:=51

        ActiveWorkbook.Close False

    Next long_col_number

    ws_console.Activate

End Sub

 

[ziv26]

Also, is there something in my code that can be optimized for speed?