Copying sheet to new workbook and saving as CSV

atblack22

New Member
Joined
Jun 16, 2011
Messages
10
I have a program that uses macros to auto populate data within a workbook into specific fields. Once it copies all information in, my macro copies all active cells and paste special values to get rid of any formulas. I then need to put this sheet into its own workbook, and then save as a CSV file and then auto email to a specific email address.

Everything works well, and it appears to save as a CSV file, BUT...my original file with all of my macros, is only about 12MB....when I save that sheet of data it saves at 41MB!!!!.... If I open that same file, and resave to a different location, it resaves to a file size of ~300KB. I need to figured out how to make it the SMALL file size constantly...any help with this???

Below is my code
Code:
Dim TempFileDate As String
    Dim ponum As String
    Dim strSend As String
 
    ponum = strFileAdd
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
 
    strSend = InputBox("Email address to send ASN", "ASN Email Address", "[EMAIL="ZZZ@ZZZ.com"]ZZZ@ZZZ.com[/EMAIL]")
    If strSend = vbNullString Then
    msbx = MsgBox("No Email selected - No Email Sent", vbOKOnly, "NO EMAIL")
    Exit Sub
    End If
    TempFileDate = Format(Now, "yyyy-mm-dd hh-mm-ss")
    TempName = ponum & TempFileDate
    On Error Resume Next
    MkDir Environ("userprofile") & "\Desktop\ASN Send"
    Sheets("ASN DATA Sheet").Select
    Sheets("ASN DATA Sheet").Copy
    ActiveWorkbook.SaveAs Filename:=Environ("userprofile") & "\Desktop\ASN Send\ASN " & TempName & ".csv", FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.SendMail strSend, "ASN Barcode Scan " & TempName
    ActiveWorkbook.Close SaveChanges:=False
 
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top