• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA help: Duplicate a worksheet between a range X to Y (1-100) using msg box

expat2013

New Member
Hi,


First time poster but many months viewer. I am having difficulty developing a very specific macro that does not seem to be difficult by conceptual design; however, I have no idea how to do it.


Background of workbook:

1) I have a tab labeled Inputs that includes the same input items for 90 different cars (in columns). Starting at cell D15 to CO15, there are numbers 1-90 and starting at cell D16 to CO16, there are the car names that correlate to the relevative number in row 15.


2) I have a seperate Calulcation tab that runs all the necessary calculations required for each car analysis that I am doing. In cell D4, I manually input the car number (1-90) and all the applicable inputs will adjust based on the number inserted into D4 and will pull the applicable information from the Inputs tab. For example, if I change the value from 1 to 2, then the 2nd car information will be pulled into the Calculation sheet from the Inputs tab.


What macro should do:

1) Have a button on the Inputs sheet that when clicked on has a message box that pops up and allows me to type in a number range between 1 and 90 (the range on Inputs tab cells D15 to CO15) - so I could type 4 to 20 and the calculation sheet will be replicated 17 times (20-4+1).


2) Instead of going in and changing the car reference number (4 to 20) manually on each of the 17 sheets in cell D4 of the applicable duplicated Calculation sheet, when the sheets are duplicated, is it possible to insert the number 4 on the first duplicated calculation sheet into cell D4, insert the number 5 on the second duplicated calculation sheet into cell D4 and etc... until the number 20 is entered?

****(The number range entered into the message box)


3) Rename all newly duplicated calculation sheets based on cell reference C4 in each of the respective calculation sheets that includes the car name. Please note the car name in C4 is based on a formula that is pulling from the Inputs sheet based on the number inputted in D4 of the applicable Calculation sheet (the car number reference)(ex. 1. David Ford Fusion).


4. Macro does not allow for duplication copies of a copied Calculation sheet to exist within the workbook, stops creating it, if it's already within the workbook (based on when renaming?) - dont know how the macro will know if the tab has already been created with the same tab name. Example (if i created 1. David Ford Fusion, then if i ever run the macro and select 1-20, the macro will realise I already have 1. David Ford Fusion and not created the calculation sheet for car 1 (based on range input within macro msg box) and has something at the end of the code that pops up a message saying "1. David Ford Fusion" was not created because it already exists within this workbook.


Note: The main/initial Calculation sheet I refer to above will always be named Calculation and onlhy used as a template to copy multiple Calculation sheets and then rename. This sheet will always be in the workbook, never deleted.


Thanks in advance and any help would be appreciated! I know this is super difficult and I plan on taking Chandoo's VBA course, but I have to get through GMAT first, then focus on VBA code.
 
Hi ,


I think the problem is somewhat complicated and your posting has coincided with the Christmas holidays ! It might be quite a while before you get any answers ; please wait.


Whenever a macro is to be developed , it is always good to have the workbook available , since no one who posts a macro wishes to do so without having tested it ; this is tedious if the coder has to recreate the worksheet tabs and the data.


You are more likely to get a solution if you can post your sample workbook which has at least a few worksheet tabs , and some sample data , so that any code that is written can be tested.


Narayan
 
Back
Top