Macro to Copy Sheet to a Network Location

[paulrh]

Multiple times per day I update a sheet within a workbook. A second sheet in the same workbook has a reduced number of cells linked to the "master" sheet I edit. I would like a macro to copy the limited data sheet to a network location from where it will be viewed by general users who do not require the full master sheet data.

The limited sheet will have a fixed name and the destination file will also be fixed, being overwritten each time I run the macro.

Thanks in advance.

Paul

 

[asdsparky]

If you add a button somewhere on the sheet you can use this:

Sub Whateveryounamethisbutton() 

Dim sFileName As String    sFileName = "sheetname" & ".xlsm" 
    ActiveSheet.SaveAs Filename:="n:\folder\subfolder\" & sFileName 

End Sub

When you add the button, it gives it a default name. You can change it if you want.
"n:" in this case will need to be changed to whatever your network drive letter is.
If you do not want the macro capabilities in the uploaded version, change the file extension to .xls

 

[asdsparky]

You can also upload it as a PDF with this code:

Sub Whateveryounamethisbutton() 

Dim sFileName As String    sFileName = "sheetname" 
    ActiveSheet.ExportAsFixedFormat _
     Type:=x1TypePDF,  Filename:="n:\folder\subfolder\" & sFileName 
     Quality:=xlQualityStandard, _
     IncludeDocProperties:=False, _
     IgnorePrintAreas:=False, _
     OpenAfterPublish:=False

End Sub

The reason I used the Dim As String line is because in my use, I also added parameters from the spreadsheet in the file nme.