Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 29th 09, 04:23 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2009
Posts: 2
Default Print Selected Items in Excel Order Form

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






  #2   Report Post  
Old December 29th 09, 04:42 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default 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,
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
  #3   Report Post  
Old December 29th 09, 05:53 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2009
Posts: 2
Default 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   Report Post  
Old December 29th 09, 06:49 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default 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   Report Post  
Old September 26th 18, 03:54 PM
Junior Member
 
First recorded activity by ExcelBanter: Sep 2018
Posts: 1
Exclamation 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
List and subtotal selected items, then print separate item list TitanG Excel Worksheet Functions 0 September 8th 08 09:07 PM
Print Pivottable - show all selected items in page filter Koen Excel Discussion (Misc queries) 0 June 19th 06 09:30 AM
How to print only selected areas from an order sheet I created. Steve2k67 Excel Discussion (Misc queries) 2 March 9th 06 11:23 AM
order form with multiple items and sizes for items Bernard56 Excel Discussion (Misc queries) 1 January 18th 06 12:43 AM
named range, data validation: list non-selected items, and new added items KR Excel Discussion (Misc queries) 1 June 24th 05 05:21 AM


All times are GMT +1. The time now is 07:23 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017