CODEWITHSUNDEEP
vbaexcel
Eric
Eric

Reputation: 105

Save AS excel file to specific location using macro

I am have a code that picks some worksheets from workbook and creates a new file. but it saves the newly generated file at random location. I would like to save the new file at specific location. Looking help and threads I have randomly used 

Application.GetSaveAsFilename

It gives me pop up window for "Save as" but it doesn't save the file in the location I have mentioned. Can anyone please see my macro and help me.

here is my code.

Sub SaveMain()

Dim Flname As String
Dim ws As Worksheet

Application.EnableEvents = False

For Each ws In ActiveWorkbook.Sheets
ws.Visible = xlSheetVisible
Next
ActiveWorkbook.Sheets("3").Range("N15").Value =   ActiveWorkbook.Sheets("Calculations").Range("W23").Value
ActiveWorkbook.Sheets("3").Range("N16").Value = ActiveWorkbook.Sheets("Calculations").Range("W28").Value
'there are some more copy cells

Flname = "Pump Datasheet-" & InputBox("Save file as") & ".xls"
Sheets(Array("Cover", "2", "3", "4", "5", "6", "7", "8", "9")).Copy
newfilename = Flname
With ActiveWorkbook
.SaveAs newfilename, FileFormat:=50
Application.GetSaveAsFilename
.Close 0
End With
For Each ws In ActiveWorkbook.Sheets
 If ws.Name <> "Main Calc" Then
 ws.Visible = xlSheetVeryHidden
End If

Next
Application.EnableEvents = True

 End Sub

Upvotes: 0

Views: 1449

Answers (2)

Dave
Dave

Reputation: 4356

Flname = "C:\temp\Pump Datasheet-" & InputBox("Save file as") & ".xls"

That will save your file as "Pump Datasheet-content from inputbox.xls" in C:\temp

Currently you don't specify a folder name, so Excel is just saving it in whatever its default save location is.

Upvotes: 0

Brian
Brian

Reputation: 2108

Try this format:

saveAsFileName = Application.GetSaveAsFilename(InitialFileName:=fName, FileFilter:="Excel Files (*.xlsx), *.xlsx")
If saveAsFileName <> "False" Then
    'Do something here
Else
    'Do something else here
End If

Upvotes: 1

Related Questions