Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
? Object What object
Putting in complete code some of my trials are (') out What I am trying to do
is have user input nbr copies then print that many. I have been able to print all with code. Problem is getting input nbr to control All assistance Greatly appreciated. Here is the complete macro Thanks Option Explicit Sub multi() 'multiple print array 'Dim pCnt As Variable Dim PrintGridlines As Variable Dim ws As Worksheets 'ws.PrintOut 'With ws Dim wCtr As Long For wCtr = 1 To 9 'With Worksheets("sheets" & wCtr)--------out of range error 'Dim i As Long 'For i = 1 To 9 Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9")).Select Dim printarea As Variable Dim pCnt As Variable With Array("Array" & wCtr) ------------'runs to this point------------------------------------ -----------'next line yellow error 424 object required-------------------------- ..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 With Sheets("Array") ActiveWindow.SelectedSheets.PrintOut Copies:=pCnt, Collate:=True End With 'Worksheets("1:9").Select 'this is code to apply ' With Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9")).Select '.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 ' With Sheets ' Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9")).Select ' ActiveWindow.SelectedSheets.PrintOut Copies:=pCnt, Collate:=True ' .pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row ' PrintGridlines = True 'End With ' ActiveSheet.pagesetup.printarea = "" ' End With ' Next wCtr ' Sheets("data").Select 'Range("E4").Select 'Next 'End Sub Worksheets("1").Select With Sheets("1") ..pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row ..PrintOut ..pagesetup.printarea = "" End With Worksheets("2").Select With Sheets("2") ..pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row ..PrintOut ..pagesetup.printarea = "" End With Worksheets("3").Select With Sheets("3") ..pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row ..PrintOut ..pagesetup.printarea = "" End With Worksheets("4").Select With Sheets("4") ..pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row ..PrintOut ..pagesetup.printarea = "" End With Worksheets("5").Select With Sheets("5") ..pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row ..PrintOut ..pagesetup.printarea = "" End With Worksheets("6").Select With Sheets("6") ..pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row ..PrintOut ..pagesetup.printarea = "" End With Worksheets("7").Select With Sheets("7") ..pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row ..PrintOut ..pagesetup.printarea = "" End With Worksheets("8").Select With Sheets("8") ..pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row ..PrintOut ..pagesetup.printarea = "" End With Worksheets("9").Select With Sheets("9") ..pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row ..PrintOut ..pagesetup.printarea = "" End With Sheets("Data").Select Range("A4").Select End With Next wCtr End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
? Object What object
Try this
Sub multi() 'multiple print array Dim ws As Worksheets Dim wCtr As Long Dim pCnt As Long pCnt = Application.InputBox("How Many Copies from 1-9", Type:=1) If pCnt < 1 Or pCnt 9 Then Exit Sub For wCtr = 1 To 9 With Sheets(CStr(wCtr)) .PageSetup.printarea = "A1:D" & .Cells(.Rows.Count, "b").End(xlUp).Row .PrintOut Copies:=pCnt .PrintOut .PageSetup.printarea = "" End With Next wCtr End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Curt" wrote in message ... Putting in complete code some of my trials are (') out What I am trying to do is have user input nbr copies then print that many. I have been able to all with code. Problem is getting input nbr to control All assistance Greatly appreciated. Here is the complete macro Thanks Option Explicit Sub multi() 'multiple print array 'Dim pCnt As Variable Dim PrintGridlines As Variable Dim ws As Worksheets 'ws.PrintOut 'With ws Dim wCtr As Long For wCtr = 1 To 9 'With Worksheets("sheets" & wCtr)--------out of range error 'Dim i As Long 'For i = 1 To 9 Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9")).Select Dim printarea As Variable Dim pCnt As Variable With Array("Array" & wCtr) ------------'runs to this point------------------------------------ -----------'next line yellow error 424 object required-------------------------- .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 With Sheets("Array") ActiveWindow.SelectedSheets.PrintOut Copies:=pCnt, Collate:=True End With 'Worksheets("1:9").Select 'this is code to apply ' With Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9")).Select '.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 ' With Sheets ' Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9")).Select ' ActiveWindow.SelectedSheets.PrintOut Copies:=pCnt, Collate:=True ' .pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row ' PrintGridlines = True 'End With ' ActiveSheet.pagesetup.printarea = "" ' End With ' Next wCtr ' Sheets("data").Select 'Range("E4").Select 'Next 'End Sub Worksheets("1").Select With Sheets("1") .pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row .PrintOut .pagesetup.printarea = "" End With Worksheets("2").Select With Sheets("2") .pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row .PrintOut .pagesetup.printarea = "" End With Worksheets("3").Select With Sheets("3") .pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row .PrintOut .pagesetup.printarea = "" End With Worksheets("4").Select With Sheets("4") .pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row .PrintOut .pagesetup.printarea = "" End With Worksheets("5").Select With Sheets("5") .pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row .PrintOut .pagesetup.printarea = "" End With Worksheets("6").Select With Sheets("6") .pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row .PrintOut .pagesetup.printarea = "" End With Worksheets("7").Select With Sheets("7") .pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row .PrintOut .pagesetup.printarea = "" End With Worksheets("8").Select With Sheets("8") .pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row .PrintOut .pagesetup.printarea = "" End With Worksheets("9").Select With Sheets("9") .pagesetup.printarea = "a1:d" & Cells(Rows.Count, "b").End(xlUp).Row .PrintOut .pagesetup.printarea = "" End With Sheets("Data").Select Range("A4").Select End With Next wCtr End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Label Options - Forms Object vs Control Box Object | Excel Discussion (Misc queries) | |||
Option button object proeprties or object not found in vba | Excel Programming | |||
Confusion about how the Window object fits into the Excel object model | Excel Programming | |||
Range object to Array object conversion | Excel Programming | |||
Range object to Array object conversion | Excel Programming |