ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Print Selected Items in Excel Order Form (https://www.excelbanter.com/excel-discussion-misc-queries/251995-print-selected-items-excel-order-form.html)

B. Costa
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

Dave Peterson
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

B. Costa
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
.

Dave Peterson
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

Josh Hurley
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


All times are GMT +1. The time now is 05:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com