VBA Save As Pop Up,

[StevenTG]

Hello,

I am trying to write a macro that runs basically an import file and saves it to a new worksheet, and once it is done running, a save dialogue box comes up, and it saves just the worksheet that has the import data. This is currently my code:

Dim varResult As Variant
Dim ActBook As Workbook

'displays the save file dialog
varResult = Application.GetSaveAsFilename(FileFilter:= _
"Excel Files (*.xlsx), *.xlsx", Title:="Save PO", _
InitialFileName:="\\showdog\service\Service_job_PO\")

'checks to make sure the user hasn't canceled the dialog
If varResult <> False Then
ActiveWorkbook.SaveAs FileName:=varResult, _
FileFormat:=xlWorkbookNormal
Exit Sub
End If



This works but, it saves the whole entire workbook including the macro rather than that one worksheet. Is there a specific coding I can use that saves the current sheet that the import file uses?

 

[Herakles]

If you use the worksheet copy method it creates a new workbook with just that worksheet in it.

This new workbook then becomes the active workbook.

The second line will save and close the workbook using the file name in varResult.

If you don't want to close the new workbook then use this line:

ActiveWorkbook.SaveAs Filename:=varResult

The new workbook will still be the active workbook.

Change the '"worksheetname' to your worksheet name.

    Worksheets("worksheetname").Copy
    
    ActiveWorkbook.Close SaveChanges:=True, Filename:=varResult

 

[StevenTG]

If you use the worksheet copy method it creates a new workbook with just that worksheet in it.

This new workbook then becomes the active workbook.

The second line will save and close the workbook using the file name in varResult.

If you don't want to close the new workbook then use this line:

ActiveWorkbook.SaveAs Filename:=varResult

The new workbook will still be the active workbook.

Change the '"worksheetname' to your worksheet name.

    Worksheets("worksheetname").Copy
   
    ActiveWorkbook.Close SaveChanges:=True, Filename:=varResult

thanks!