Automatically create and fill sheets and then save as seperate files

**Focus_Kevin** · 02-26-2010, 12:09 PM

I am trying to get excel to create trade tickets as new Sheets based upon information I paste into sheet1. This information includes(In this order) Date, Account, Source, Quantity, Commission, FBSI Shortname, Amount, Txn Code, Txn Key, Symbol, Price, Interest Amount, Comments, Settlement Date, Broker, Cusip, Security Description, Security Type, Fund ID, Key Code, Core Fund, and TPA Order Guide.
I would only like to reference the date, acct #, Quantity, commission, FBSI Shortname, Amount, TXN Key, Symbol, and Price when creating the new sheets.
The trade ticket is already designed in sheet two 'MasterTicket':
Date (m/dd/yyyy)=D2
Price ($)=F5
Symbol (Ticker Symbol)=B6
Security Descript (Text)=C6
Acct#(AlphaNumeric)=A9
Txn Key=E5(In Large Caps if possible)
Type(#)=B9:B?
Quantity(# w/5Decimals)=C9:C?
Commission($)=D9:D?
FBSI Shortname(text)= E9:E?
Amount($)=F9:F?
At the bottom of the data is a row that totals the Quantity and Amount that needs to always be there.
Then I'd like to save each sheet as a seperate file named-
(YYYY)-(mm)-(dd) "Block Trading Form"(TXN Key)(Symbol)

**JBeaucaire** · 02-26-2010, 02:00 PM

Sounds perfectly doable. Post up that workbook with a good set of testing data in sheet1 and we'll take a look.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

**Focus_Kevin** · 02-26-2010, 04:24 PM

Thanks for responding so quick! Here it is

I've Included sheets that have all the symbols I work with, as well as an options sheet that has the Underlying symbol as the header, with all available option symbols in each respective column. Can these be dynamic lists?
Also, the (YYYY-MM-DD) for the file name should be the date of the transaction, not the date the file is created.

**JBeaucaire** · 02-26-2010, 05:26 PM

Sorry, I don't see what you want help with. You want a macro that fills out the SymbolMaster sheet with information from the Test Data sheet? Then copy the SymbolMaster sheet off to a workbook of it's own and save it?

What is the basis? Can you fill out 2-3 example sheets so we can try to see your logic in action? That will make automating it easier.

**Focus_Kevin** · 02-26-2010, 08:57 PM

I created all the tickets that would be from the data. I also included some notes in cells, as well as on a seperate sheet.

This is my first VBA project...I hope I'm helping...

**Focus_Kevin** · 03-01-2010, 11:26 AM

I used this macro to sort the data into proper groups.
Sub TradeDataSorter()
'
' TradeDataSorter Macro
' Macro recorded 3/1/2010 by kevin
'

'
Cells.Select
Selection.Sort Key1:=Range("P2"), Order1:=xlAscending, Key2:=Range("I2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub