For the attached spreadsheet (MC Consolidated Example.xlsm) when button 'Push to Book 1' is clicked it imports the rows from that sheet to another (Book1 Example.xls) WHERE the date in Col J in MC Consolidated equals today's date.
From there I manually copy what is in Book 1 and paste it into another sheet and save that sheet as Mastercard - smTODAY'S DATE.xls. I'd rather just skip the import to Book 1 and go directly to the Mastercard - sm worksheet.
Here's what I need 'Push to Book 1' to do instead:
As you can see, the data imported to 'Mastercard - smSept9-11-Example.xls' from 'MC Consolidated' has changed slightly: there are three zeroes added to the Credit Card number and the date from Col J is not imported, but other than that everything is exactly the same.
What I need now instead is for when button 'Push to Book 1' is clicked:
1. open up another workbook
2. import data from MC Consolidated where the date in Col J is today's date
3. add three zeroes to the front of the card number (and NOT allowing the cell to convert to scientific notation as it wants to do.) -FYI: the credit card numbers do NOT have a dash in them. I inserted in order to change the numbers.
4. make sure the imported to Col E has two decimal places and does not round off
5. populate the top row with the colors and columns headings showing in ''Mastercard - smSept9-11-Example.xls
6. save this now newly created spreadsheet with the date from Col J so that if that date were 9/9/2011 the spreadsheet would save as: 'Mastercard - smSept9-11.xls'
added bonus if possible, (can ask another related question for this part if desired):
7. after doing point 6 above, then open a new email message (outlook 2007)
8. add the attachment of this newly created Mastercard - sm spreadsheet to it
9. add to the body of the email message the below text:
Please see attached file for regular load request.
Please let me know you received this email.
10. insert into the subject line this text:
MC - smTODAY'S DATE (example: MC-smSept10-11)
MC-Consolidated-Example.xlsm Book1-Example.xls Mastercard---smSept9-11-Example.xls