Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Print Selected Items in Excel Order Form
Hi,
Im new to the site and also to Visual Basic. I have found the following code from Dave Peterson on http://www.contextures.com/xlForm03.html, which does more or less what I want apart from one thing, which I cant figure out how to change. Values from the worksheet list, starting in Column B and moving right, are copied to the order form, into the addresses specified. Instead of copying values from Column B and moving right (as in B1, C1, D1...), I would like to copy to the form only selected cells like B1, D1, H1... Print Marked Items Code Option Base 0 Sub PrintUsingDatabase() Dim FormWks As Worksheet Dim DataWks As Worksheet Dim myRng As Range Dim myCell As Range Dim iCtr As Long Dim myAddresses As Variant Dim lOrders As Long Set FormWks = Worksheets("Order Form") Set DataWks = Worksheets("Orders") myAddresses = Array("E5", "E6", "B10", "E25", "B16", "C16", "D16") With DataWks 'first row of data to last row of data in column B Set myRng = .Range("B3", .Cells(.Rows.Count, "B").End(xlUp)) End With For Each myCell In myRng.Cells With myCell If IsEmpty(.Offset(0, -1)) Then 'if the row is not marked, do nothing Else .Offset(0, -1).ClearContents 'clear mark for the next time For iCtr = LBound(myAddresses) To UBound(myAddresses) FormWks.Range(myAddresses(iCtr)).Value _ = myCell.Offset(0, iCtr).Value Next iCtr Application.Calculate 'just in case 'after testing, change to Preview to False to Print FormWks.PrintOut Preview:=True lOrders = lOrders + 1 End If End With Next myCell MsgBox lOrders & " orders were printed." End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Print Selected Items in Excel Order Form
This retrieves the values from the cells based on the offset from column B.
myAddresses = Array("E5", "E6", "B10", "E25", "B16", "C16", "D16") myOffsets = Array(0, 1, 3, 5, 9, 15, 33) So column B (offset 0) goes to E5 column C (offset 1) goes to E6 column E (offset 3) goes to B10 ... Column AI (offset 33) goes to D16 Option Base 0 Option Explicit Sub PrintUsingDatabase() Dim FormWks As Worksheet Dim DataWks As Worksheet Dim myRng As Range Dim myCell As Range Dim iCtr As Long Dim myAddresses As Variant Dim myOffsets As Variant 'from column B Dim lOrders As Long Set FormWks = Worksheets("Order Form") Set DataWks = Worksheets("Orders") myAddresses = Array("E5", "E6", "B10", "E25", "B16", "C16", "D16") myOffsets = Array(0, 1, 3, 5, 9, 15, 33) If UBound(myOffsets) < UBound(myAddresses) Then MsgBox "Design error!" Exit Sub End If With DataWks 'first row of data to last row of data in column B Set myRng = .Range("B3", .Cells(.Rows.Count, "B").End(xlUp)) End With For Each myCell In myRng.Cells With myCell If IsEmpty(.Offset(0, -1)) Then 'if the row is not marked, do nothing Else .Offset(0, -1).ClearContents 'clear mark for the next time For iCtr = LBound(myAddresses) To UBound(myAddresses) FormWks.Range(myAddresses(iCtr)).Value _ = myCell.Offset(0, myOffsets(iCtr)).Value Next iCtr Application.Calculate 'just in case 'after testing, change to Preview to False to Print FormWks.PrintOut Preview:=True lOrders = lOrders + 1 End If End With Next myCell MsgBox lOrders & " orders were printed." End Sub B. Costa wrote: Hi, Im new to the site and also to Visual Basic. I have found the following code from Dave Peterson on http://www.contextures.com/xlForm03.html, which does more or less what I want apart from one thing, which I cant figure out how to change. Values from the worksheet list, starting in Column B and moving right, are copied to the order form, into the addresses specified. Instead of copying values from Column B and moving right (as in B1, C1, D1...), I would like to copy to the form only selected cells like B1, D1, H1... Print Marked Items Code Option Base 0 Sub PrintUsingDatabase() Dim FormWks As Worksheet Dim DataWks As Worksheet Dim myRng As Range Dim myCell As Range Dim iCtr As Long Dim myAddresses As Variant Dim lOrders As Long Set FormWks = Worksheets("Order Form") Set DataWks = Worksheets("Orders") myAddresses = Array("E5", "E6", "B10", "E25", "B16", "C16", "D16") With DataWks 'first row of data to last row of data in column B Set myRng = .Range("B3", .Cells(.Rows.Count, "B").End(xlUp)) End With For Each myCell In myRng.Cells With myCell If IsEmpty(.Offset(0, -1)) Then 'if the row is not marked, do nothing Else .Offset(0, -1).ClearContents 'clear mark for the next time For iCtr = LBound(myAddresses) To UBound(myAddresses) FormWks.Range(myAddresses(iCtr)).Value _ = myCell.Offset(0, iCtr).Value Next iCtr Application.Calculate 'just in case 'after testing, change to Preview to False to Print FormWks.PrintOut Preview:=True lOrders = lOrders + 1 End If End With Next myCell MsgBox lOrders & " orders were printed." End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Print Selected Items in Excel Order Form
Hi Dave
Never thought I would get the reply from the man himself, I have tried the below and it works brilliantly, thanks for the quick reply and for writing the code in the first place. Kind Regards Bruno "Dave Peterson" wrote: This retrieves the values from the cells based on the offset from column B. myAddresses = Array("E5", "E6", "B10", "E25", "B16", "C16", "D16") myOffsets = Array(0, 1, 3, 5, 9, 15, 33) So column B (offset 0) goes to E5 column C (offset 1) goes to E6 column E (offset 3) goes to B10 ... Column AI (offset 33) goes to D16 Option Base 0 Option Explicit Sub PrintUsingDatabase() Dim FormWks As Worksheet Dim DataWks As Worksheet Dim myRng As Range Dim myCell As Range Dim iCtr As Long Dim myAddresses As Variant Dim myOffsets As Variant 'from column B Dim lOrders As Long Set FormWks = Worksheets("Order Form") Set DataWks = Worksheets("Orders") myAddresses = Array("E5", "E6", "B10", "E25", "B16", "C16", "D16") myOffsets = Array(0, 1, 3, 5, 9, 15, 33) If UBound(myOffsets) < UBound(myAddresses) Then MsgBox "Design error!" Exit Sub End If With DataWks 'first row of data to last row of data in column B Set myRng = .Range("B3", .Cells(.Rows.Count, "B").End(xlUp)) End With For Each myCell In myRng.Cells With myCell If IsEmpty(.Offset(0, -1)) Then 'if the row is not marked, do nothing Else .Offset(0, -1).ClearContents 'clear mark for the next time For iCtr = LBound(myAddresses) To UBound(myAddresses) FormWks.Range(myAddresses(iCtr)).Value _ = myCell.Offset(0, myOffsets(iCtr)).Value Next iCtr Application.Calculate 'just in case 'after testing, change to Preview to False to Print FormWks.PrintOut Preview:=True lOrders = lOrders + 1 End If End With Next myCell MsgBox lOrders & " orders were printed." End Sub B. Costa wrote: Hi, I€„¢m new to the site and also to Visual Basic. I have found the following code from Dave Peterson on http://www.contextures.com/xlForm03.html, which does more or less what I want apart from one thing, which I can€„¢t figure out how to change. Values from the worksheet list, starting in Column B and moving right, are copied to the order form, into the addresses specified. Instead of copying values from Column B and moving right (as in B1, C1, D1...), I would like to copy to the form only selected cells like B1, D1, H1... Print Marked Items Code Option Base 0 Sub PrintUsingDatabase() Dim FormWks As Worksheet Dim DataWks As Worksheet Dim myRng As Range Dim myCell As Range Dim iCtr As Long Dim myAddresses As Variant Dim lOrders As Long Set FormWks = Worksheets("Order Form") Set DataWks = Worksheets("Orders") myAddresses = Array("E5", "E6", "B10", "E25", "B16", "C16", "D16") With DataWks 'first row of data to last row of data in column B Set myRng = .Range("B3", .Cells(.Rows.Count, "B").End(xlUp)) End With For Each myCell In myRng.Cells With myCell If IsEmpty(.Offset(0, -1)) Then 'if the row is not marked, do nothing Else .Offset(0, -1).ClearContents 'clear mark for the next time For iCtr = LBound(myAddresses) To UBound(myAddresses) FormWks.Range(myAddresses(iCtr)).Value _ = myCell.Offset(0, iCtr).Value Next iCtr Application.Calculate 'just in case 'after testing, change to Preview to False to Print FormWks.PrintOut Preview:=True lOrders = lOrders + 1 End If End With Next myCell MsgBox lOrders & " orders were printed." End Sub -- Dave Peterson . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Print Selected Items in Excel Order Form
Glad it worked for you.
B. Costa wrote: Hi Dave Never thought I would get the reply from the man himself, I have tried the below and it works brilliantly, thanks for the quick reply and for writing the code in the first place. Kind Regards Bruno "Dave Peterson" wrote: This retrieves the values from the cells based on the offset from column B. myAddresses = Array("E5", "E6", "B10", "E25", "B16", "C16", "D16") myOffsets = Array(0, 1, 3, 5, 9, 15, 33) So column B (offset 0) goes to E5 column C (offset 1) goes to E6 column E (offset 3) goes to B10 ... Column AI (offset 33) goes to D16 Option Base 0 Option Explicit Sub PrintUsingDatabase() Dim FormWks As Worksheet Dim DataWks As Worksheet Dim myRng As Range Dim myCell As Range Dim iCtr As Long Dim myAddresses As Variant Dim myOffsets As Variant 'from column B Dim lOrders As Long Set FormWks = Worksheets("Order Form") Set DataWks = Worksheets("Orders") myAddresses = Array("E5", "E6", "B10", "E25", "B16", "C16", "D16") myOffsets = Array(0, 1, 3, 5, 9, 15, 33) If UBound(myOffsets) < UBound(myAddresses) Then MsgBox "Design error!" Exit Sub End If With DataWks 'first row of data to last row of data in column B Set myRng = .Range("B3", .Cells(.Rows.Count, "B").End(xlUp)) End With For Each myCell In myRng.Cells With myCell If IsEmpty(.Offset(0, -1)) Then 'if the row is not marked, do nothing Else .Offset(0, -1).ClearContents 'clear mark for the next time For iCtr = LBound(myAddresses) To UBound(myAddresses) FormWks.Range(myAddresses(iCtr)).Value _ = myCell.Offset(0, myOffsets(iCtr)).Value Next iCtr Application.Calculate 'just in case 'after testing, change to Preview to False to Print FormWks.PrintOut Preview:=True lOrders = lOrders + 1 End If End With Next myCell MsgBox lOrders & " orders were printed." End Sub B. Costa wrote: Hi, I€„¢m new to the site and also to Visual Basic. I have found the following code from Dave Peterson on http://www.contextures.com/xlForm03.html, which does more or less what I want apart from one thing, which I can€„¢t figure out how to change. Values from the worksheet list, starting in Column B and moving right, are copied to the order form, into the addresses specified. Instead of copying values from Column B and moving right (as in B1, C1, D1...), I would like to copy to the form only selected cells like B1, D1, H1... Print Marked Items Code Option Base 0 Sub PrintUsingDatabase() Dim FormWks As Worksheet Dim DataWks As Worksheet Dim myRng As Range Dim myCell As Range Dim iCtr As Long Dim myAddresses As Variant Dim lOrders As Long Set FormWks = Worksheets("Order Form") Set DataWks = Worksheets("Orders") myAddresses = Array("E5", "E6", "B10", "E25", "B16", "C16", "D16") With DataWks 'first row of data to last row of data in column B Set myRng = .Range("B3", .Cells(.Rows.Count, "B").End(xlUp)) End With For Each myCell In myRng.Cells With myCell If IsEmpty(.Offset(0, -1)) Then 'if the row is not marked, do nothing Else .Offset(0, -1).ClearContents 'clear mark for the next time For iCtr = LBound(myAddresses) To UBound(myAddresses) FormWks.Range(myAddresses(iCtr)).Value _ = myCell.Offset(0, iCtr).Value Next iCtr Application.Calculate 'just in case 'after testing, change to Preview to False to Print FormWks.PrintOut Preview:=True lOrders = lOrders + 1 End If End With Next myCell MsgBox lOrders & " orders were printed." End Sub -- Dave Peterson . -- Dave Peterson |
#5
|
|||
|
|||
Run-Time error '1004'
Dave,
I'm attempting to use your code to quickly print data in my form template, however, some of my rows do not contain data, for example: I have a section for 2 buyers but in most deals there is only 1 buyer and an amount, so my section for my second buyer is left blank. When running your code, I keep getting a ***run-time error '1004', Method 'Range' of object'_Worksheet' failed*** on this string: FormWks.Range(myAddresses(iCtr)).Value _ = myCell.Offset(0, iCtr).Value I'm not sure what to do at this point. Here is my VBA code I am using: Option Explicit ' Developed by Contextures Inc. ' www.contextures.com Option Base 0 Sub PrintUsingDatabase() Dim FormWks As Worksheet Dim DataWks As Worksheet Dim myRng As Range Dim myCell As Range Dim iCtr As Long Dim myAddresses As Variant Dim lOrders As Long Set FormWks = Worksheets("Printout") Set DataWks = Worksheets("Active") myAddresses = Array("C4", "C5", "C7", "C9", "", "C10", "C11", "C12", "C14", "", "C15", "C16", "C17", "C39", "C19", "C20", "C21", "C23", "C24", "C25") With DataWks 'first row of data to last row of data in column B Set myRng = .Range("B4", .Cells(.Rows.Count, "B").End(xlUp)) End With For Each myCell In myRng.Cells With myCell If IsEmpty(.Offset(0, -1)) Then 'if the row is not marked, do nothing Else .Offset(0, -1).ClearContents 'clear mark for the next time For iCtr = LBound(myAddresses) To UBound(myAddresses) FormWks.Range(myAddresses(iCtr)).Value _ = myCell.Offset(0, iCtr).Value Next iCtr Application.Calculate 'just in case 'after testing, change to Preview to False to Print FormWks.PrintOut Preview:=True lOrders = lOrders + 1 End If End With Next myCell MsgBox lOrders & " Records were printed." End Sub Any and all help is greatly appreciated!!! Thanks, Josh |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
List and subtotal selected items, then print separate item list | Excel Worksheet Functions | |||
Print Pivottable - show all selected items in page filter | Excel Discussion (Misc queries) | |||
How to print only selected areas from an order sheet I created. | Excel Discussion (Misc queries) | |||
order form with multiple items and sizes for items | Excel Discussion (Misc queries) | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) |