Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA - array or collection literals? | Excel Programming | |||
Error 1004 when setting a Series Collection.Values as Array via VB | Excel Programming | |||
Collection vs. Array of Arrays, nszim | Excel Programming | |||
VSTO: Sheets collection a 1-based array(?!?!?) | Excel Programming | |||
Writing to An Array or Collection | Excel Programming |