VBA Help Needed

[Closed]
Report
Posts
1
Registration date
Wednesday April 19, 2017
Status
Member
Last seen
April 19, 2017
-
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
-
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


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.



Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
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!
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
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
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