Using VBA to open a new workbook, copy data, rename a sheet, and save the new workbook as a variable-based name.

AndersonA

New Member
Joined
Oct 19, 2017
Messages
2
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)
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top