Exporting Sheet to new file

youngda

Board Regular
Joined
Jun 13, 2006
Messages
81
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?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,079
Office Version
  1. 365
Platform
  1. Windows
I think this is the line of code you are looking for:
Code:
    ActiveWorkbook.SaveAs Filename:="C:\Custodians\Schwab\" & Format(Date, "mmddyyy") & ".csv", FileFormat:= _
        xlCSVMSDOS, CreateBackup:=False
 

youngda

Board Regular
Joined
Jun 13, 2006
Messages
81
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,079
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jun 13, 2006
Messages
81

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,079
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,079
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
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.
 

Forum statistics

Threads
1,141,718
Messages
5,708,068
Members
421,543
Latest member
SGM

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
Top