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" & 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

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" & 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

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" & 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

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



"Curt" <(E-Mail Removed)> wrote in message
news:E07448F7-14ED-4D55-9D5D-(E-Mail Removed)...
> 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" & 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

sometimes we just can't see
Thanks

"George Nicholson" wrote:

>
> > .pagesetup.printarea = "A1" & Cells(Rows.Count, "B").End(xlUp).Row
> ..Cells (missing the dot)
>
>
>
> "Curt" <(E-Mail Removed)> wrote in message
> news:E07448F7-14ED-4D55-9D5D-(E-Mail Removed)...
> > 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" & 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
>
>
>