related question - next step in process from last question
I have a workbook that has a sheet named 'Visa Consolidated'. I need a button on that sheet, that when clicked does the following WHEN date in Col F = today's date:
1. Opens up a new spreadsheet (example attached: Visa - smDec16-11-example.xls)
2. Pastes value from Col B (from Visa Consolidated) to Col A (Visa - smDec16-11-example.xls)
3. Pastes Value from Col E (from Visa Consolidated) to Col C (Visa - smDec16-11-example.xls)
4. Insert text 'EUR' into Col B (from Visa - smDec16-11-example.xls)
5. Insert text: 'smCurrentYearMonthDate1' into Col D (of Visa - smDec16-11-example.xls), where 'smCurrentYearMonthDate1' would format as: sm201112161. 2011 is tomorrow's year, 12 is tomorrow's month and 16 is tomorrow's date and 1 is the first in the sequence of numbers. (sm201112161, sm201112162, sm201112163, etc.)
If tomorrow's date was January 5, 2012, the value in Col D would be: 'sm201201051. If tomorrow's date were
February 18, 2012 Col D would be: sm201202181)
HOWEVER tomorrow's date cannot be a Saturday or Sunday. If tomorrow's date falls on a Saturday or Sunday it must go to Monday's date.
6. Save the file as "Visa - Sovereign smTomorrow's Date-year.xls" (example: 'Visa - smDec16-11.xls' If tomorrow's date was January 5, 2012, the file would be saved as: 'Visa - smJan05-12.xls')
HOWEVER it CANNOT save the file with a date that falls on a Saturday or Sunday. If tomorrow's date falls on a
Saturday or Sunday it must go to Monday's date. It is saved to this folder:
C:\Users\Michael\Desktop\M
- saved, but is kept open in case edits have to be made.
Visa---smDec16-11-example.xls
NEXT STEP WILL BE ANOTHER RELATED QUESTION: TO ATTACH THE NEWLY CREATED SPREADSHEET TO AN EMAIL AND SEND IT TO A SPECIFIC EMAIL ADDRESS WITH A SUBJECT AND BODY
1. Opens up a new spreadsheet (example attached: Visa - smDec16-11-example.xls)
2. Pastes value from Col B (from Visa Consolidated) to Col A (Visa - smDec16-11-example.xls)
3. Pastes Value from Col E (from Visa Consolidated) to Col C (Visa - smDec16-11-example.xls)
4. Insert text 'EUR' into Col B (from Visa - smDec16-11-example.xls)
5. Insert text: 'smCurrentYearMonthDate1' into Col D (of Visa - smDec16-11-example.xls), where 'smCurrentYearMonthDate1' would format as: sm201112161. 2011 is tomorrow's year, 12 is tomorrow's month and 16 is tomorrow's date and 1 is the first in the sequence of numbers. (sm201112161, sm201112162, sm201112163, etc.)
If tomorrow's date was January 5, 2012, the value in Col D would be: 'sm201201051. If tomorrow's date were
February 18, 2012 Col D would be: sm201202181)
HOWEVER tomorrow's date cannot be a Saturday or Sunday. If tomorrow's date falls on a Saturday or Sunday it must go to Monday's date.
6. Save the file as "Visa - Sovereign smTomorrow's Date-year.xls" (example: 'Visa - smDec16-11.xls' If tomorrow's date was January 5, 2012, the file would be saved as: 'Visa - smJan05-12.xls')
HOWEVER it CANNOT save the file with a date that falls on a Saturday or Sunday. If tomorrow's date falls on a
Saturday or Sunday it must go to Monday's date. It is saved to this folder:
C:\Users\Michael\Desktop\M
- saved, but is kept open in case edits have to be made.
Visa---smDec16-11-example.xls
NEXT STEP WILL BE ANOTHER RELATED QUESTION: TO ATTACH THE NEWLY CREATED SPREADSHEET TO AN EMAIL AND SEND IT TO A SPECIFIC EMAIL ADDRESS WITH A SUBJECT AND BODY
ASKER
Hi gowflow. Any progress with this one?
Yes working on it
gowflow
gowflow
Just to make sure I understood well in Col D of the new file you want sm+value of next working day +sequential number from 1 to total number of records is that correct ?
like if you have 10 items in this file and next day is 12/28/2011 and is not Sat or Sun then the value in Col D would be
sm201112281
sm201112282
sm201112283
sm201112284
sm201112285
sm201112286
sm201112287
sm201112288
sm201112289
sm2011122910
Correct ?
and an other one the Sheet name would be in today's date ( and the file name would be in Next working day date is that ok ?
gowflow
like if you have 10 items in this file and next day is 12/28/2011 and is not Sat or Sun then the value in Col D would be
sm201112281
sm201112282
sm201112283
sm201112284
sm201112285
sm201112286
sm201112287
sm201112288
sm201112289
sm2011122910
Correct ?
and an other one the Sheet name would be in today's date ( and the file name would be in Next working day date is that ok ?
gowflow
ASKER
That is correct on the sequential number.
Not sure what you mean by "sheet name" but the file name would be the next working day.
Not sure what you mean by "sheet name" but the file name would be the next working day.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sheet name is sheet name like Main is a sheet name Visa consolidated is sheet name the smnextworkingday.xls is workbookname !
gowlfow
gowlfow
ASKER
Oh, I see what you mean by sheet name. I know what sheet name means, but I didn't know what sheet you were talking about.
So... it worked perfectly except for a few minor issues:
1. The sheet name needs to be the same date as the file name. So, running the code today (12-27-2011) the sheet name should be 12-28-2011, the next business day.
Also, the Amount brought over from Col E in Visa Consolidated is not always in a two decimal format. (I know, you did not write that code.) However, when it is published to the sheet you just created, the amount brought into Col C needs to be formatted as currency with only 2 decimals, if possible.
One other point is that I'd really rather not have the newly created sheet saved in the Western Union folder. I try to keep things organized and separated. I already have the Access database there, which isn't where it use to be, and I'd rather not have this sheet in there as well.
Where it needs to go is C:\Users\Michael\Desktop\M
Let me know.
Thank you, gowflow. Works wonderful. After this step is done, then we'll want to attach it to an email, with a specific subject and body.
So... it worked perfectly except for a few minor issues:
1. The sheet name needs to be the same date as the file name. So, running the code today (12-27-2011) the sheet name should be 12-28-2011, the next business day.
Also, the Amount brought over from Col E in Visa Consolidated is not always in a two decimal format. (I know, you did not write that code.) However, when it is published to the sheet you just created, the amount brought into Col C needs to be formatted as currency with only 2 decimals, if possible.
One other point is that I'd really rather not have the newly created sheet saved in the Western Union folder. I try to keep things organized and separated. I already have the Access database there, which isn't where it use to be, and I'd rather not have this sheet in there as well.
Where it needs to go is C:\Users\Michael\Desktop\M
Let me know.
Thank you, gowflow. Works wonderful. After this step is done, then we'll want to attach it to an email, with a specific subject and body.
well I understand that it get messy if you mix files in dffrent directories. I can add same as we did for Western Union a location for the Visa file is this what you want ?
for the other items it is already fixed, will wait for your reply to put alltogether.
For the email I understand once this one is done you said previoously that you will post a realted question right ?
gowflow
for the other items it is already fixed, will wait for your reply to put alltogether.
For the email I understand once this one is done you said previoously that you will post a realted question right ?
gowflow
Sorry looking at the issue if we need to add the folder I prefer it is in a separate quesiton (not for points) but as the details on how to implement will be quite lengthy as it affect several parts of the workbook.
And if you prefer it is hard coded then let it be, but I warn you I do not like thesehard coded issues as they open the door for playing in the code that could be detrimental at some point in time if not done by knowledgable person, anyway you have if I recall well experienced bad implementation of code which resulted in your workbook beeing ruined !!! so think well about it.
gowflow
And if you prefer it is hard coded then let it be, but I warn you I do not like thesehard coded issues as they open the door for playing in the code that could be detrimental at some point in time if not done by knowledgable person, anyway you have if I recall well experienced bad implementation of code which resulted in your workbook beeing ruined !!! so think well about it.
gowflow
ASKER
Yes, once this is done I will post a related question to email the file.
In regards to where and how to save the newly created spreadsheet, I'll defer to you. It does NOT need to be a hardcoded location, but I'd prefer to not have to designate the place to save it every time I open up the workbook as I have to do for the Western Union file (which I'll ask to have fixed in a related question.)
So, I will pose the saving file method and location in a related question and add the updates to the code to it as well.
In regards to where and how to save the newly created spreadsheet, I'll defer to you. It does NOT need to be a hardcoded location, but I'd prefer to not have to designate the place to save it every time I open up the workbook as I have to do for the Western Union file (which I'll ask to have fixed in a related question.)
So, I will pose the saving file method and location in a related question and add the updates to the code to it as well.
ASKER
Wonderful work, gowflow. There seems to be nothing you can't do. I am greatly appreciative.
gowflow