Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable row selection and printing
I have a workbook with two worksheets: sheet1, sheet2
I need a macro that selects sheet1 as the active sheet. I will need to enter a starting and ending row using input boxes. A message box should pop-up asking me to confirm the rows selected and prompting me to print the rows. For each row, copy only cells a:u. These cells will be pasted as values and transposed vertically to a form on sheet2 in cells c3:c23. I will always print sheet2 on a single page portrait however, I need to select the default before the first page is printed. Subsequent rows will print to the default printer. This loop should continue until all rows are transposed and printed. Finally, I need a message box to display the rows selected and a print count. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable row selection and printing
On Apr 10, 10:40*am, MarcusA wrote:
I have a workbook with two worksheets: *sheet1, sheet2 I need a macro that selects sheet1 as the active sheet. *I will need to enter a starting and ending row using input boxes. * A message box should pop-up asking me to confirm the rows selected and prompting me to print the rows. For each row, copy only cells a:u. *These cells will be pasted as values and transposed vertically to a form on sheet2 in cells c3:c23. *I will always print sheet2 on a single page portrait however, I need to select the default before the first page is printed. *Subsequent rows will print to the default printer. * This loop should continue until all rows are transposed and printed. Finally, I need a message box to display the rows selected and a print count. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable row selection and printing
Here is the code I have been using to copy, transpose and print individual
rows of data. Sub Don() Sheets("Open Items").Activate Dim r1 As Range, r2 As Range, rf As Range Dim i As Long, j As Long, k As Long, l As Long Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Unprotect Password:="cashops" Next ws i = Application.InputBox("enter starting column number 1-256", 1) j = Application.InputBox("enter starting row as number 1-65536", 1) k = Application.InputBox("enter ending column character 1-256", 1) l = Application.InputBox("enter ending row as number 1-65536", 1) Set r1 = Cells(j, i) Set r2 = Cells(l, k) Set rf = Range(r1, r2) MsgBox (rf.Address) If MsgBox("Transpose and print this row?", vbYesNo Or vbQuestion _ Or vbDefaultButton2) = vbYes Then rf.Copy Sheets("Lbx Payment Resolution Form").Select Range("c3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True For Each ws In ThisWorkbook.Worksheets ws.Protect Password:="cashops" Next ws End If For Each ws In ThisWorkbook.Worksheets ws.Protect Password:="cashops" Sheets("Lbx Payment Resolution Form").Select Next ws Application.Dialogs(xlDialogPrint).Show End Sub I need to change my selection criteria to enter a starting and ending row then for each row, copy-paste value-transpose the select cells (a1:c1) from 'Open Items Worksheet' to 'Lbx Worksheet' in cells c3:c23. The macro will print the 'Lbx Worksheet' and pull the next row of data from the 'Open Items Worksheet' until it reaches my ending row. "cht13er" wrote: On Apr 10, 10:40 am, MarcusA wrote: I have a workbook with two worksheets: sheet1, sheet2 I need a macro that selects sheet1 as the active sheet. I will need to enter a starting and ending row using input boxes. A message box should pop-up asking me to confirm the rows selected and prompting me to print the rows. For each row, copy only cells a:u. These cells will be pasted as values and transposed vertically to a form on sheet2 in cells c3:c23. I will always print sheet2 on a single page portrait however, I need to select the default before the first page is printed. Subsequent rows will print to the default printer. This loop should continue until all rows are transposed and printed. Finally, I need a message box to display the rows selected and a print count. Thanks What's the question? Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
printing selection | Excel Discussion (Misc queries) | |||
Using a variable to determine row selection | Excel Programming | |||
Variable Selection Summation | Excel Programming | |||
Selection of variable rows | Excel Programming | |||
Variable for column selection | Excel Programming |