VBA Help Needed [Closed]

jetski1202 1 Posts Wednesday April 19, 2017Registration date April 19, 2017 Last seen - Apr 19, 2017 at 08:17 AM - Latest reply: TrowaD 2369 Posts Sunday September 12, 2010Registration dateModeratorStatus June 21, 2018 Last seen
- Apr 24, 2017 at 11:47 AM
Hello,
I have written code to prompt the user to state how many new sheets they want to add. But I need for the data on the "Main" sheet to always be copied to the new sheets. How do I do this? Also, how do I set the first code (Asking how many sheets they need) to appear when the file is opened?

Thanks!
See more 

5 replies

ac3mark 9622 Posts Monday June 3, 2013Registration dateModeratorStatus June 22, 2018 Last seen - Apr 19, 2017 at 03:59 PM
0
Thank you
Place the code to prompt into the worksheet method of WORKBOOK_OPEN(). There are bunch of built in methods.

As for the other code, post back with what you have, and we can help with that. Have you looked at this link to see if any of the examples here will help?
http://ccm.net/faq/53497-manipulating-entries-in-excel-via-vba

DisqualifiedEntryText(dqstCnt) = ThisWorkbook.Worksheets("Qualifiers").Range("M" & 8 + dqstCnt).value


...should help with how to programmaticaly build ranges to copy.



TrowaD 2369 Posts Sunday September 12, 2010Registration dateModeratorStatus June 21, 2018 Last seen - Apr 20, 2017 at 10:59 AM
0
Thank you
Hi Jetski,

'But I need for the data on the "Main" sheet to always be copied to the new sheets. How do I do this?'


You probably use something like:
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "NewSheet"


Instead of adding a sheet, copy your Main sheet:
Sheets("Main").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = "NewSheet"



Best regards,
Trowa
TrowaD,

That somewhat worked, but if the user states they need multiple sheets, it still will only add 1 sheet no matter what number they put in. How do I correct this? Other than that it worked.

Thanks!
TrowaD 2369 Posts Sunday September 12, 2010Registration dateModeratorStatus June 21, 2018 Last seen - Apr 24, 2017 at 11:47 AM
hmmm, you said:

'I have written code to prompt the user to state how many new sheets they want to add.'

So I thought you already got that part covered.

Can you make ac3mark's suggestion work?

Best regards,
Trowa
ac3mark 9622 Posts Monday June 3, 2013Registration dateModeratorStatus June 22, 2018 Last seen - Updated by ac3mark on 21/04/17 at 12:55 PM
0
Thank you
If I may....

Initilaze a variable and store an input. Loop the number of times the variable is. something like:

dim IntNumberOfSheets, CountofLoop
IntNumberOfSheets = InputBox("how many?", "how many?", 1, xpos, ypos)

for CountofLoop = 0 to IntNumberOfsheets - 1
Call Doyourcodehere
next


I hope this helps.

FYI, the above code CANNOT be cut and pasted into your project, as it wil not run as written. It can be quickly, and easilty made to work, you just have to define the remaining variables, and write the Routine Doyourcodehere!

Have FUN!


It's kind of fun to do the impossible! -Walter Elias Disney