Exporting Sheet to new file

[youngda]

I am trying to find a way to export a sheet of data to a new file. The file needs to be saved as (today's date).CSV and I'd like to perform the whole exercise with a simple click of a button. I'd also like it to save automatically. The sheet name in my case is "Schwab Output", and for example purposes I only want to save in "C:\Custodians\Schwab\(Todays Date).csv" I've tried using the recorder several times but each time it doesn't quite work correctly. Can anybody help?

 

[Joe4]

I think this is the line of code you are looking for:

    ActiveWorkbook.SaveAs Filename:="C:\Custodians\Schwab\" & Format(Date, "mmddyyy") & ".csv", FileFormat:= _
        xlCSVMSDOS, CreateBackup:=False

 

[youngda]

Here's what I have so far...

Cool, thanks for that line. I'm having two problems. 1. I can't seem to paste the range that I select in the first line in my new workbook.

Secondly, although the macro is trying to save the file as I want it too, it gives me an error saying the file is read-only, even though its a new workbook. Know why?



Worksheets("Schwab Output").Range("a1", Cells(Worksheets("Advisor View").Range("Outputtotal").Value, 5)).Copy

Workbooks.Add.Activate
ActiveSheet.Paste


ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\David Young.BBFG\Desktop\Schwab\" & Format(Date, "mmddyyyy") & "Schwab Trades.csv", FileFormat:= _
xlCSVMSDOS, CreateBackup:=False

 

[Joe4]

I am a little confused by your Copy Range statement. Which worksheet is your data on? You aren't trying to go across worksheets, are you?

You may want to try Activating/Selecting the sheet you wish to copy the information From first before trying to copy the data.

 

[youngda]

Confusing indeed

Basically the data comes from the user in a graphically friendly sheet called "adviser view". The reason I reference that is because on the adviser view a range is displayed showing the total number of trades the user has inputed. I n my "Schwab output sheet", i have a series of formulas that change the users inputs into data recognized by the Schwab trading system. I am then, through the click of a button, exporting this data to a separate excel sheet where it will be uploaded into the Schwab system. I have to do it this b/c if there is any extra data, even a blank formula, in the sheet I upload to Schwab the upload fails. So I ahve to be sure I am pasting the only the relevant data that has been produced by my code. I am relatively sure that I am copying my range alright, its just the pasting that doesn't work. I have to go, but ill keep in touch tomorrow as I work on this. Thanks for the help.

 

[Joe4]

Open up the VB Explorer where you enter the code and resize the both to about a quarter the size of the screen so you can see both the Excel sheet and VBA code.

Then by using the F8 key, you can Step through your code line-by-line, and see exactly what is happening. This may help in pinpointing exactly where the problem is occurring.

 

[Joe4]

I still am unsure of your range copy statement. To make sure it is working as intended, you can break it up into 2 statements:

Worksheets("Schwab Output").Range("a1", Cells(Worksheets("Advisor View").Range("Outputtotal").Value, 5)).Select
Selection.Copy

Now, if you step through the code using the method I proposed in the previous post, you should actually see the range being selected before it is copied. Then you can confirm that it is selecting the correct area to copy.