Collection verses array
 

have code that will print a worksheet asking for nbe copies then do it. no
problem. Am trying to do the same thing with a group of ( 9 ) sheets tabs are
(1-9) useing my print code seems to fail in this application.
End result I am aiming for is user selects nbr copies then program prints
nbr copies of the (9) sheets. I have been able toprint all (9) doing a single
print Want to have user only select nbr copies not have to reprint many
times. Hope i am clear.
Sample of my efforts Worksheets("1").select is repeated for all (9) sheets
..pagesetup is where it hangs now .Should I be useing a collection instead of
array
Thanks to all
Dim printarea As Variable
Dim pCnt As Variable
..pagesetup.printarea = "A1:D" & Cells(Rows.Count, "B").End(xlUp).Row
pCnt = Application.InputBox("How Many Copies from 1-9", Type:=1)
If pCnt < 1 Or pCnt 9 Then Exit Sub
Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9")).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=pCnt, Collate:=True

Worksheets("1").Select
With Sheets("1")
..pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row
..PrintOut
..pagesetup.printarea = ""
End With

Collection verses array
 

If you are trying to set the print area of multiple sheet, you need to do it
individually to each sheet.

if you are trying to select the first 9 sheets in the tab order, you would use

Sheets(Array(1, 2, 3, 4, 5, 6, 7, 8, 9)).Select

the way you show it, your sheets should be named 1, 2, etc.

--
Regards,
Tom Ogilvy


"Curt" wrote:

have code that will print a worksheet asking for nbe copies then do it. no
problem. Am trying to do the same thing with a group of ( 9 ) sheets tabs are
(1-9) useing my print code seems to fail in this application.
End result I am aiming for is user selects nbr copies then program prints
nbr copies of the (9) sheets. I have been able toprint all (9) doing a single
print Want to have user only select nbr copies not have to reprint many
times. Hope i am clear.
Sample of my efforts Worksheets("1").select is repeated for all (9) sheets
.pagesetup is where it hangs now .Should I be useing a collection instead of
array
Thanks to all
Dim printarea As Variable
Dim pCnt As Variable
.pagesetup.printarea = "A1:D" & Cells(Rows.Count, "B").End(xlUp).Row
pCnt = Application.InputBox("How Many Copies from 1-9", Type:=1)
If pCnt < 1 Or pCnt 9 Then Exit Sub
Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9")).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=pCnt, Collate:=True

Worksheets("1").Select
With Sheets("1")
.pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row
.PrintOut
.pagesetup.printarea = ""
End With

Collection verses array
 

have set area for each and can print. No problem printing one copie of each
Am trying to get as i have on single sheet user ask for 3 copies it prints 3
copies of all sheets. Hope this tells it better. As I have on worksheet (1)
is same on all (9)

"Tom Ogilvy" wrote:

If you are trying to set the print area of multiple sheet, you need to do it
individually to each sheet.

if you are trying to select the first 9 sheets in the tab order, you would use

Sheets(Array(1, 2, 3, 4, 5, 6, 7, 8, 9)).Select

the way you show it, your sheets should be named 1, 2, etc.

--
Regards,
Tom Ogilvy


"Curt" wrote:

have code that will print a worksheet asking for nbe copies then do it. no
problem. Am trying to do the same thing with a group of ( 9 ) sheets tabs are
(1-9) useing my print code seems to fail in this application.
End result I am aiming for is user selects nbr copies then program prints
nbr copies of the (9) sheets. I have been able toprint all (9) doing a single
print Want to have user only select nbr copies not have to reprint many
times. Hope i am clear.
Sample of my efforts Worksheets("1").select is repeated for all (9) sheets
.pagesetup is where it hangs now .Should I be useing a collection instead of
array
Thanks to all
Dim printarea As Variable
Dim pCnt As Variable
.pagesetup.printarea = "A1:D" & Cells(Rows.Count, "B").End(xlUp).Row
pCnt = Application.InputBox("How Many Copies from 1-9", Type:=1)
If pCnt < 1 Or pCnt 9 Then Exit Sub
Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9")).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=pCnt, Collate:=True

Worksheets("1").Select
With Sheets("1")
.pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row
.PrintOut
.pagesetup.printarea = ""
End With

Collection verses array
 

.pagesetup.printarea = "A1:D" & Cells(Rows.Count, "B").End(xlUp).Row
..Cells (missing the dot)



"Curt" wrote in message
...
have code that will print a worksheet asking for nbe copies then do it. no
problem. Am trying to do the same thing with a group of ( 9 ) sheets tabs
are
(1-9) useing my print code seems to fail in this application.
End result I am aiming for is user selects nbr copies then program prints
nbr copies of the (9) sheets. I have been able toprint all (9) doing a
single
print Want to have user only select nbr copies not have to reprint many
times. Hope i am clear.
Sample of my efforts Worksheets("1").select is repeated for all (9)
sheets
.pagesetup is where it hangs now .Should I be useing a collection instead
of
array
Thanks to all
Dim printarea As Variable
Dim pCnt As Variable
.pagesetup.printarea = "A1:D" & Cells(Rows.Count, "B").End(xlUp).Row
pCnt = Application.InputBox("How Many Copies from 1-9", Type:=1)
If pCnt < 1 Or pCnt 9 Then Exit Sub
Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9")).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=pCnt, Collate:=True

Worksheets("1").Select
With Sheets("1")
.pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row
.PrintOut
.pagesetup.printarea = ""
End With

Collection verses array
 

sometimes we just can't see
Thanks

"George Nicholson" wrote:


.pagesetup.printarea = "A1:D" & Cells(Rows.Count, "B").End(xlUp).Row
..Cells (missing the dot)



"Curt" wrote in message
...
have code that will print a worksheet asking for nbe copies then do it. no
problem. Am trying to do the same thing with a group of ( 9 ) sheets tabs
are
(1-9) useing my print code seems to fail in this application.
End result I am aiming for is user selects nbr copies then program prints
nbr copies of the (9) sheets. I have been able toprint all (9) doing a
single
print Want to have user only select nbr copies not have to reprint many
times. Hope i am clear.
Sample of my efforts Worksheets("1").select is repeated for all (9)
sheets
.pagesetup is where it hangs now .Should I be useing a collection instead
of
array
Thanks to all
Dim printarea As Variable
Dim pCnt As Variable
.pagesetup.printarea = "A1:D" & Cells(Rows.Count, "B").End(xlUp).Row
pCnt = Application.InputBox("How Many Copies from 1-9", Type:=1)
If pCnt < 1 Or pCnt 9 Then Exit Sub
Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9")).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=pCnt, Collate:=True

Worksheets("1").Select
With Sheets("1")
.pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row
.PrintOut
.pagesetup.printarea = ""
End With