I am starting to learn a process that my co-worker has done manually for years. As I learn the process, I need to automate the process to eliminate the possibility of human error.
I don't have the most expansive knowledge of VBA and have only done a few basic (free) online courses. I'm going to try to go through "Power Programming...", but need some help in the mean time.
So I've already got macros written to consolidate the sales data between our different product lines. (i.e. duplicate the data onto separate sheets, rename the sheets, and delete the unneeded data)
The next step is to create a new workbook for each of the sheets I've created, create/rename the sheets within that workbook, copy the data over, and save the workbook under a specified name.
The caveat with naming the workbook is that it needs to be variable based. What I am envisioning (but I'm not sure I know if it is possible) is to use the InputBox command to have the user effectively tell the macro what to name the workbook. Each workbook will have some default character names, but I'll need to differentiate between 3 mo. data, 6 mo. data, and 12 mo. data along with the fiscal year.
Basically, when naming the new workbook, it'll look something like "ProductName only CM Raw Data xxmo FYXXXX.xlsm" where the xxmo is the number of months of data and the FYXXXX is the fiscal year.
Another thing I feel I should make note of is that the new workbooks will all have a new folder location within our server. These folders will not change and are predefined, but I need to specify the locations as well. (e.g. "T:\Product Management\Product Line Strategies\ProductName")
My current situation is that I have a separate macro for the consolidation of each product line and I use a short macro to input prompts and call all of the macros in succession. I will, ideally, continue to address each product family one-by-one to avoid confusion for the future.
I can stumble through a good bit of what is needed by recording keystrokes and modifying the syntax to fit my needs. (trial and error)
I don't have the most expansive knowledge of VBA and have only done a few basic (free) online courses. I'm going to try to go through "Power Programming...", but need some help in the mean time.
So I've already got macros written to consolidate the sales data between our different product lines. (i.e. duplicate the data onto separate sheets, rename the sheets, and delete the unneeded data)
The next step is to create a new workbook for each of the sheets I've created, create/rename the sheets within that workbook, copy the data over, and save the workbook under a specified name.
The caveat with naming the workbook is that it needs to be variable based. What I am envisioning (but I'm not sure I know if it is possible) is to use the InputBox command to have the user effectively tell the macro what to name the workbook. Each workbook will have some default character names, but I'll need to differentiate between 3 mo. data, 6 mo. data, and 12 mo. data along with the fiscal year.
Basically, when naming the new workbook, it'll look something like "ProductName only CM Raw Data xxmo FYXXXX.xlsm" where the xxmo is the number of months of data and the FYXXXX is the fiscal year.
Another thing I feel I should make note of is that the new workbooks will all have a new folder location within our server. These folders will not change and are predefined, but I need to specify the locations as well. (e.g. "T:\Product Management\Product Line Strategies\ProductName")
My current situation is that I have a separate macro for the consolidation of each product line and I use a short macro to input prompts and call all of the macros in succession. I will, ideally, continue to address each product family one-by-one to avoid confusion for the future.
I can stumble through a good bit of what is needed by recording keystrokes and modifying the syntax to fit my needs. (trial and error)
