Exporting Single Sheet to Network Path

[jamesmev]

I am currently working on a workbook that all I need exported into a network path is the first worksheet.
I am able to get this to save and move. However, it is saving on the main file. It is not saving within the folder I need it saved at.

Below is the code I am using...
**
Sub Mail_ActiveSheetDT()


Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object




With Application
.ScreenUpdating = False
.EnableEvents = False
End With


Set Sourcewb = ActiveWorkbook


'Copy the ActiveSheet to a new workbook
Sheets("Combined").Copy
Set Destwb = ActiveWorkbook


'Determine the Excel version and file extension/format
With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007-2016
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End With


'Change all cells in the worksheet to values if you want
With Destwb.Sheets(1).UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False


'Save the new workbook/Mail it/Delete it
TempFilePath = Environ$("temp") & ""
TempFileName = "Downtime Form" & " " & Format(Now, "dd-mmm-yy")


Dim SaveName As String
SaveName = ActiveSheet.Range("A1").Text
ActiveWorkbook.SaveAs Filename:="C:\Users\xxxx\Desktop\TEST" & _
SaveName & ".xls"
End Sub ***

 

[rpaulson]

Sub Create_single_file()
Dim rs As Worksheet
Set rs = Worksheets("Sheet1") 'adjust name as needed
myPath = "\\nas\documents\" ' adjust path as needed
myFile = myPath & "File.xlsx"

rs.Cells.Copy

Set NewBook = Workbooks.add
NewBook.Worksheets("Sheet1").Range("A1").Select ' Special (xlPasteValues)(xlPasteformat)
ActiveSheet.Paste

NewBook.SaveAs FileName:=myFile
ActiveWorkbook.Close
End Sub

hth,

Ross