Report

VBA Help Needed

Ask a question jetski1202 1Posts Wednesday April 19, 2017Registration date April 19, 2017 Last seen - Last answered on Apr 24, 2017 at 11:47 AM by TrowaD
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!
Helpful
+0
plus moins
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.



Leave a comment
Helpful
+0
plus moins
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
jetski1202- Apr 20, 2017 at 01:55 PM
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!
Reply
TrowaD 2282Posts Sunday September 12, 2010Registration date ModeratorStatus October 19, 2017 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
Reply
Leave a comment
Helpful
+0
plus moins
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!


Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!