Repost for additional information

[Guest]

Hello
Sorry for the repost but the my OP got buried last week and Im still confused...

I am saving one sheet Table! from my spreadsheet as an .xls and also as .csv with the following code...

ScreenUpdating = Fals
Worksheets("Table").Cop
ActiveWorkbook.SaveAs "\\Dfs01\shares\Groupdirs\0535\Table" & Range("B56") & ".xls
ActiveWorkbook.SaveAs "g:\data\table" & Range("B56") & ".xls
ActiveWorkbook.SendMail Recipients:=Array("My distribution list"
ActiveSheet.Cop
ActiveWorkbook.SaveAs "\\Dfs01\shares\Groupdirs\0535\Table" & Range("B56")" & Range("B56") & ".csv", FileFormat:=xlCS
ActiveWorkbook.Close SaveChanges:=Fals
ActiveWorkbook.Close SaveChanges:=Fals
ScreenUpdating = Tru

End Su


In the save I want to save just the values no links, no macros, et
Don Guillett (Not Bob) Graciousliy provided the following code to accomplish this but I have tried adding it in several places and cant get it to work. Where do I need to put it in my original code

With ActiveSheet.UsedRang
...Value = .Valu
End Wit

Thanks
Henr

 

[Dave Peterson]

Maybe:

option explicit
sub testme01()

dim wks as worksheet
dim actWkbk as workbook

set actwkbk = activeworkbook
with actwkbk
.worksheets("table").copy
end with

set wks = activesheet 'in the new workbook
with wks.usedrange
.value = .value
end with
wks.parent.saveAs "\\your unc path"
wks.parent.saveas "yourmapped path"
wks.parent.sendmail recipients:=array("hi","there")
'no need to copy it again
wks.parent.saveas "\\unc\" & ... & ".csv, fileformat:=xlcsv
wks.parent.close savechanges:=false
actwkbk.activate

end sub

Watch for typos.
ScreenUpdating = False
Worksheets("Table").Copy
ActiveWorkbook.SaveAs "\\Dfs01\shares\Groupdirs\0535\Table" & Range("B56") &
".xls"
ActiveWorkbook.SaveAs "g:\data\table" & Range("B56") & ".xls"
ActiveWorkbook.SendMail Recipients:=Array("My distribution list")
ActiveSheet.Copy
ActiveWorkbook.SaveAs "\\Dfs01\shares\Groupdirs\0535\Table" & Range("B56")"
& Range("B56")
& ".csv", FileFormat:=xlCSV
ActiveWorkbook.Close SaveChanges:=False
ActiveWorkbook.Close SaveChanges:=False
ScreenUpdating = True