Praveen Bj
07-05-2012, 08:56 PM
Hello,
I want to create a macro which helps me create 4 separate files based on the data in column AG in one tab. In column AG there are 3 kinds of data as show below.
1. Approved
2. Current Code issue
3. Old Code issue
Before macro starts to create these files I want macro to do certain basic formatting checks:
1. Macro should convert the contents in Column E to numbers (as some times numbers are saved in text format), I want the macro to change the format of the contents to number.
2. Macro should check whether there are any duplicate values in column H, as there should not be any duplicate values in this column.
a. If there are duplicate values the macro should give an error message "Duplicate Tracker ID found". And macro should stop proceeding further.
b. If there are no duplicate values found the macro should proceed to next step.
3. Macro should check whether the hours in Column AB are rounded to nearest 0.50.
a. If there are odd decimal hours (which are not rounded to 0.5 or nearest round number). The macro should give an error message "Hours not rounded properly". And macro should stop proceeding further.
b. If the hours are rounded properly the macro should proceed to next step.
If all the above criteria's are fulfilled then the macro should proceed further to create the following 4 separate files as bellow.
1. First file should include data related to the following data contained in column AG
a. Approved
b. Old Code Issue
Save the file with the file name as "Current Hours"
2. Second file should include the data related to the following data contained in column AG
a. Approved
b. Current Code Issue
Save the file with the file name as "Approved Hours"
3. Third file should include data related to the following data contained in column AG
a. Current Code issue
Save the file with the file name as "Current Code issue"
4. Fourth file should include data related to the following data contained in column AG
a. Old Code Issue.
Save the file with the file name as "Old Code issue"
Also the macro should prompt for selecting the folder to save these 4 files.
Note: I want the macro to create all the four tabs in any case. (I.e even if any of the following values are not found in column AG, still the macro should create all the 4 files):
1. Approved
2. Current Code issue
3. Old Code issue
I have included the example files, for your easy reference.
Example of Input file:
1. Manager Approved Hours.xlsx
Examples of desired final Output files created using the input file:
1. Current Hours.xlsx
2. Approved Hours.xlsx
3. Current Code issue.xlsx
4. Old Code issue.xlsx
Thank you in advance for your help,
Praveen B J
I want to create a macro which helps me create 4 separate files based on the data in column AG in one tab. In column AG there are 3 kinds of data as show below.
1. Approved
2. Current Code issue
3. Old Code issue
Before macro starts to create these files I want macro to do certain basic formatting checks:
1. Macro should convert the contents in Column E to numbers (as some times numbers are saved in text format), I want the macro to change the format of the contents to number.
2. Macro should check whether there are any duplicate values in column H, as there should not be any duplicate values in this column.
a. If there are duplicate values the macro should give an error message "Duplicate Tracker ID found". And macro should stop proceeding further.
b. If there are no duplicate values found the macro should proceed to next step.
3. Macro should check whether the hours in Column AB are rounded to nearest 0.50.
a. If there are odd decimal hours (which are not rounded to 0.5 or nearest round number). The macro should give an error message "Hours not rounded properly". And macro should stop proceeding further.
b. If the hours are rounded properly the macro should proceed to next step.
If all the above criteria's are fulfilled then the macro should proceed further to create the following 4 separate files as bellow.
1. First file should include data related to the following data contained in column AG
a. Approved
b. Old Code Issue
Save the file with the file name as "Current Hours"
2. Second file should include the data related to the following data contained in column AG
a. Approved
b. Current Code Issue
Save the file with the file name as "Approved Hours"
3. Third file should include data related to the following data contained in column AG
a. Current Code issue
Save the file with the file name as "Current Code issue"
4. Fourth file should include data related to the following data contained in column AG
a. Old Code Issue.
Save the file with the file name as "Old Code issue"
Also the macro should prompt for selecting the folder to save these 4 files.
Note: I want the macro to create all the four tabs in any case. (I.e even if any of the following values are not found in column AG, still the macro should create all the 4 files):
1. Approved
2. Current Code issue
3. Old Code issue
I have included the example files, for your easy reference.
Example of Input file:
1. Manager Approved Hours.xlsx
Examples of desired final Output files created using the input file:
1. Current Hours.xlsx
2. Approved Hours.xlsx
3. Current Code issue.xlsx
4. Old Code issue.xlsx
Thank you in advance for your help,
Praveen B J