VBA Help Needed

Closed
jetski1202 Posts 1 Registration date Wednesday April 19, 2017 Status Member Last seen April 19, 2017 - Apr 19, 2017 at 08:17 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - 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!

3 replies

Blocked Profile
Apr 19, 2017 at 03:59 PM
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?
https://ccm.net/faq/53497-how-to-manipulate-data-in-excel-using-vba

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


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



0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Apr 20, 2017 at 10:59 AM
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
0
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!
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
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
0
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
0