Trying to "Auto-Create" 40 Sheets

A

anticore

New Member

Joined

Aug 28, 2009

Messages

16

• Aug 28, 2009

• #2

what if i create a sub

Sub createReport()

create a new book,
copy the report sheet into the new book
and make a loop, changing the value of the field that determins what "sales group" is listed

would that work?
if so, can someone provide some guidence

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Aug 28, 2009

• #3

Hello and welcome to MrExcel.

Details are needed:

Which cell contains the data validation?

What is the range that defines the data validation list?

The name of the sheet might also be useful to know.

 

Upvote 0

A

anticore

New Member

Joined

Aug 28, 2009

Messages

16

• Aug 28, 2009

• #4

sheet = report

validation cell is G5 in report

range: =salesGroup

 

Upvote 0

A

anticore

New Member

Joined

Aug 28, 2009

Messages

16

• Aug 28, 2009

• #5

just in case =salesGroup is db_salesGroup!,B2:B23

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Aug 28, 2009

• #6

Perhaps this ... press ALT + F11 to open the Visual Basic Editor, from the Insert menu select Module then paste the following into thw white space on the right

Code:

Sub Display()
Dim response As VbMsgBoxResult, c As Range
response = MsgBox("Click Yes to print or No to view", vbQuestion + vbYesNo)
With Sheets("report")
    For Each c In Range("salesGroup")
        .Range("G5") = c.Value
        Select Case response
            Case vbYes: .PrintOut
            Case vbNo: .PrintPreview
        End Select
    Next c
End With
End Sub

Press ALT + Q to close the code window then Tools >: Macro > Macros, click on Display then click the Run button.

 

Upvote 0

A

anticore

New Member

Joined

Aug 28, 2009

Messages

16

• Aug 28, 2009

• #7

thats amazing, simpler than i thought it'd be, thanks

whats the the "c" for though? in "c As Range"

 

Upvote 0

A

anticore

New Member

Joined

Aug 28, 2009

Messages

16

• Aug 28, 2009

• #8

anticore said:

thats amazing, simpler than i thought it'd be, thanks

whats the the "c" for though? in "c As Range"

Click to expand...

nevermind, found it, just temp var almost right?

 

Upvote 0

A

anticore

New Member

Joined

Aug 28, 2009

Messages

16

• Aug 28, 2009

• #9

how would i add a refresh between the call of each sheet?

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Aug 28, 2009

• #10

It is basically saying for each cell in that named range but Excel does not have a Cell object so I use c to save on the typing.

If you wish you can add a button from the Forms toolbar and assign this macro when prompted.

 

Upvote 0