Hello,

I am trying to create a packing list of items needed for an order. I
currently have a spreadsheet that lists all available items in various sizes
etc. Instead of printing off this entire sheet, which would go accross two
pages and down two pages I would like a form created which will list(print)
only the items selected.

I am running Excel 2003, XP Pro. The current worksheet contains columns for
the item description a blank column for the quantity that the Sales Rep
enters in, along with the cost and selling price of that item. There are
over 200 items. The items are fairly static, in other words, we don't add
parts too frequently, so it's not real dynamic. The set of four columns then
repeats across the width of the sheet with additonal items and information.

I'm looking for a printout of only those items where the Sales Rep has
entered in the quantity. I assume this can be done via a macro, however I am
unsure of the exact langauage to use - something along the lines of:

Look at a list of specific cells for the quantity entered.

If Quantity is non blank, copy description (which will be in a cell directly
to the left of the quantity cell) and the quantity to another worksheet
within the same workbook.

Go to next quantity cell location and repeat until done.

The result would then be my "packing list" which would list only those
items, along with the quantity, on a separate worksheet so that I could print
that off.

The quantity cell locations are in more than one column, and would almost
have to be manually entered in to be checked for quantity in the macro.

At some point in the future I am planning on re-vamping this form so that
the quantities are all in the same column, I assume this task would be much
easier if this were the case.

Thanks in advance for all your help and suggestions!

Mark

Just a suggestion...

I'd spend time rearranging the data so that the quantities go in one column.

Then I'd apply Data|Filter|autofilter to that column with the quantities.

You can filter to show non-blanks (or greater than 0) and print that.

It may be easier to implement, too. (And less work that has to be thrown
away???)

MarkT wrote:

Hello,

I am trying to create a packing list of items needed for an order. I
currently have a spreadsheet that lists all available items in various sizes
etc. Instead of printing off this entire sheet, which would go accross two
pages and down two pages I would like a form created which will list(print)
only the items selected.

I am running Excel 2003, XP Pro. The current worksheet contains columns for
the item description a blank column for the quantity that the Sales Rep
enters in, along with the cost and selling price of that item. There are
over 200 items. The items are fairly static, in other words, we don't add
parts too frequently, so it's not real dynamic. The set of four columns then
repeats across the width of the sheet with additonal items and information.

I'm looking for a printout of only those items where the Sales Rep has
entered in the quantity. I assume this can be done via a macro, however I am
unsure of the exact langauage to use - something along the lines of:

Look at a list of specific cells for the quantity entered.

If Quantity is non blank, copy description (which will be in a cell directly
to the left of the quantity cell) and the quantity to another worksheet
within the same workbook.

Go to next quantity cell location and repeat until done.

The result would then be my "packing list" which would list only those
items, along with the quantity, on a separate worksheet so that I could print
that off.

The quantity cell locations are in more than one column, and would almost
have to be manually entered in to be checked for quantity in the macro.

At some point in the future I am planning on re-vamping this form so that
the quantities are all in the same column, I assume this task would be much
easier if this were the case.

Thanks in advance for all your help and suggestions!

Mark

--

Dave Peterson

Hi Dave,

Thanks for your input, I certainly understand where you are coming from as
that was what I wanted to do as well. However, this spreadsheet is the basis
for about eight other attached worksheets that gather information on pricing,
remanufacturing and inventory. This is a project of mine to do some time in
2007, however I was looking to see if there was some way to do this without
revamping the current layout.

Thanks again for your thoughts!

Mark

"Dave Peterson" wrote:

Just a suggestion...

I'd spend time rearranging the data so that the quantities go in one column.

Then I'd apply Data|Filter|autofilter to that column with the quantities.

You can filter to show non-blanks (or greater than 0) and print that.

It may be easier to implement, too. (And less work that has to be thrown
away???)

MarkT wrote:

Hello,

I am trying to create a packing list of items needed for an order. I
currently have a spreadsheet that lists all available items in various sizes
etc. Instead of printing off this entire sheet, which would go accross two
pages and down two pages I would like a form created which will list(print)
only the items selected.

I am running Excel 2003, XP Pro. The current worksheet contains columns for
the item description a blank column for the quantity that the Sales Rep
enters in, along with the cost and selling price of that item. There are
over 200 items. The items are fairly static, in other words, we don't add
parts too frequently, so it's not real dynamic. The set of four columns then
repeats across the width of the sheet with additonal items and information.

I'm looking for a printout of only those items where the Sales Rep has
entered in the quantity. I assume this can be done via a macro, however I am
unsure of the exact langauage to use - something along the lines of:

Look at a list of specific cells for the quantity entered.

If Quantity is non blank, copy description (which will be in a cell directly
to the left of the quantity cell) and the quantity to another worksheet
within the same workbook.

Go to next quantity cell location and repeat until done.

The result would then be my "packing list" which would list only those
items, along with the quantity, on a separate worksheet so that I could print
that off.

The quantity cell locations are in more than one column, and would almost
have to be manually entered in to be checked for quantity in the macro.

At some point in the future I am planning on re-vamping this form so that
the quantities are all in the same column, I assume this task would be much
easier if this were the case.

Thanks in advance for all your help and suggestions!

Mark

--

Dave Peterson

Maybe...

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim RptWks As Worksheet
Dim iRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim FirstCol As Long
Dim HowManyQtyCols As Long
Dim HowManyColsPerGroup As Long
Dim DestCell As Range
Dim qtyCol As Long

Set CurWks = Worksheets("sheet1")
Set RptWks = Worksheets("sheet2")

'first cell to get data on the report worksheet
Set DestCell = RptWks.Range("A11")

With CurWks
'first column with quantities
FirstCol = 1 'column A

'first row with qty
FirstRow = 6
'use the second column (desc) to get the last row for all columns?
LastRow = .Cells(.Rows.Count, FirstCol + 1).End(xlUp).Row

'4 qty columns
HowManyQtyCols = 4

'how many columns per group
HowManyColsPerGroup = 4

For iCol = 1 To HowManyQtyCols
qtyCol = FirstCol + (HowManyColsPerGroup * (iCol - 1))
For iRow = FirstRow To LastRow
If .Cells(iRow, qtyCol).Value = 0 Then
'do nothing
Else
DestCell.Resize(1, HowManyColsPerGroup).Value _
= .Cells(iRow, qtyCol) _
.Resize(1, HowManyColsPerGroup).Value

'get ready for the next non-zero one
Set DestCell = DestCell.Offset(1, 0)
End If
Next iRow
Next iCol
End With

End Sub


MarkT wrote:

Hi Dave,

Thanks for your input, I certainly understand where you are coming from as
that was what I wanted to do as well. However, this spreadsheet is the basis
for about eight other attached worksheets that gather information on pricing,
remanufacturing and inventory. This is a project of mine to do some time in
2007, however I was looking to see if there was some way to do this without
revamping the current layout.

Thanks again for your thoughts!

Mark

"Dave Peterson" wrote:

Just a suggestion...

I'd spend time rearranging the data so that the quantities go in one column.

Then I'd apply Data|Filter|autofilter to that column with the quantities.

You can filter to show non-blanks (or greater than 0) and print that.

It may be easier to implement, too. (And less work that has to be thrown
away???)

MarkT wrote:

Hello,

I am trying to create a packing list of items needed for an order. I
currently have a spreadsheet that lists all available items in various sizes
etc. Instead of printing off this entire sheet, which would go accross two
pages and down two pages I would like a form created which will list(print)
only the items selected.

I am running Excel 2003, XP Pro. The current worksheet contains columns for
the item description a blank column for the quantity that the Sales Rep
enters in, along with the cost and selling price of that item. There are
over 200 items. The items are fairly static, in other words, we don't add
parts too frequently, so it's not real dynamic. The set of four columns then
repeats across the width of the sheet with additonal items and information.

I'm looking for a printout of only those items where the Sales Rep has
entered in the quantity. I assume this can be done via a macro, however I am
unsure of the exact langauage to use - something along the lines of:

Look at a list of specific cells for the quantity entered.

If Quantity is non blank, copy description (which will be in a cell directly
to the left of the quantity cell) and the quantity to another worksheet
within the same workbook.

Go to next quantity cell location and repeat until done.

The result would then be my "packing list" which would list only those
items, along with the quantity, on a separate worksheet so that I could print
that off.

The quantity cell locations are in more than one column, and would almost
have to be manually entered in to be checked for quantity in the macro.

At some point in the future I am planning on re-vamping this form so that
the quantities are all in the same column, I assume this task would be much
easier if this were the case.

Thanks in advance for all your help and suggestions!

Mark

--

Dave Peterson


--

Dave Peterson

Dave,

I'm new to VBA, so it looks like I will be breaking open my new book from
Walkenhbach - "Excel 2003 Power Programming with VBA" tonight!

I will give your code a try.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim RptWks As Worksheet
Dim iRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim FirstCol As Long
Dim HowManyQtyCols As Long
Dim HowManyColsPerGroup As Long
Dim DestCell As Range
Dim qtyCol As Long

Set CurWks = Worksheets("sheet1")
Set RptWks = Worksheets("sheet2")

'first cell to get data on the report worksheet
Set DestCell = RptWks.Range("A11")

With CurWks
'first column with quantities
FirstCol = 1 'column A

'first row with qty
FirstRow = 6
'use the second column (desc) to get the last row for all columns?
LastRow = .Cells(.Rows.Count, FirstCol + 1).End(xlUp).Row

'4 qty columns
HowManyQtyCols = 4

'how many columns per group
HowManyColsPerGroup = 4

For iCol = 1 To HowManyQtyCols
qtyCol = FirstCol + (HowManyColsPerGroup * (iCol - 1))
For iRow = FirstRow To LastRow
If .Cells(iRow, qtyCol).Value = 0 Then
'do nothing
Else
DestCell.Resize(1, HowManyColsPerGroup).Value _
= .Cells(iRow, qtyCol) _
.Resize(1, HowManyColsPerGroup).Value

'get ready for the next non-zero one
Set DestCell = DestCell.Offset(1, 0)
End If
Next iRow
Next iCol
End With

End Sub


MarkT wrote:

Hi Dave,

Thanks for your input, I certainly understand where you are coming from as
that was what I wanted to do as well. However, this spreadsheet is the basis
for about eight other attached worksheets that gather information on pricing,
remanufacturing and inventory. This is a project of mine to do some time in
2007, however I was looking to see if there was some way to do this without
revamping the current layout.

Thanks again for your thoughts!

Mark

"Dave Peterson" wrote:

Just a suggestion...

I'd spend time rearranging the data so that the quantities go in one column.

Then I'd apply Data|Filter|autofilter to that column with the quantities.

You can filter to show non-blanks (or greater than 0) and print that.

It may be easier to implement, too. (And less work that has to be thrown
away???)

MarkT wrote:

Hello,

I am trying to create a packing list of items needed for an order. I
currently have a spreadsheet that lists all available items in various sizes
etc. Instead of printing off this entire sheet, which would go accross two
pages and down two pages I would like a form created which will list(print)
only the items selected.

I am running Excel 2003, XP Pro. The current worksheet contains columns for
the item description a blank column for the quantity that the Sales Rep
enters in, along with the cost and selling price of that item. There are
over 200 items. The items are fairly static, in other words, we don't add
parts too frequently, so it's not real dynamic. The set of four columns then
repeats across the width of the sheet with additonal items and information.

I'm looking for a printout of only those items where the Sales Rep has
entered in the quantity. I assume this can be done via a macro, however I am
unsure of the exact langauage to use - something along the lines of:

Look at a list of specific cells for the quantity entered.

If Quantity is non blank, copy description (which will be in a cell directly
to the left of the quantity cell) and the quantity to another worksheet
within the same workbook.

Go to next quantity cell location and repeat until done.

The result would then be my "packing list" which would list only those
items, along with the quantity, on a separate worksheet so that I could print
that off.

The quantity cell locations are in more than one column, and would almost
have to be manually entered in to be checked for quantity in the macro.

At some point in the future I am planning on re-vamping this form so that
the quantities are all in the same column, I assume this task would be much
easier if this were the case.

Thanks in advance for all your help and suggestions!

Mark

--

Dave Peterson


--

Dave Peterson

Remember to backup often and save before you experiment.

It's easy to close without saving after you've screwed things up, er, modified
in the wrong direction.

MarkT wrote:

Dave,

I'm new to VBA, so it looks like I will be breaking open my new book from
Walkenhbach - "Excel 2003 Power Programming with VBA" tonight!

I will give your code a try.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim RptWks As Worksheet
Dim iRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim FirstCol As Long
Dim HowManyQtyCols As Long
Dim HowManyColsPerGroup As Long
Dim DestCell As Range
Dim qtyCol As Long

Set CurWks = Worksheets("sheet1")
Set RptWks = Worksheets("sheet2")

'first cell to get data on the report worksheet
Set DestCell = RptWks.Range("A11")

With CurWks
'first column with quantities
FirstCol = 1 'column A

'first row with qty
FirstRow = 6
'use the second column (desc) to get the last row for all columns?
LastRow = .Cells(.Rows.Count, FirstCol + 1).End(xlUp).Row

'4 qty columns
HowManyQtyCols = 4

'how many columns per group
HowManyColsPerGroup = 4

For iCol = 1 To HowManyQtyCols
qtyCol = FirstCol + (HowManyColsPerGroup * (iCol - 1))
For iRow = FirstRow To LastRow
If .Cells(iRow, qtyCol).Value = 0 Then
'do nothing
Else
DestCell.Resize(1, HowManyColsPerGroup).Value _
= .Cells(iRow, qtyCol) _
.Resize(1, HowManyColsPerGroup).Value

'get ready for the next non-zero one
Set DestCell = DestCell.Offset(1, 0)
End If
Next iRow
Next iCol
End With

End Sub


MarkT wrote:

Hi Dave,

Thanks for your input, I certainly understand where you are coming from as
that was what I wanted to do as well. However, this spreadsheet is the basis
for about eight other attached worksheets that gather information on pricing,
remanufacturing and inventory. This is a project of mine to do some time in
2007, however I was looking to see if there was some way to do this without
revamping the current layout.

Thanks again for your thoughts!

Mark

"Dave Peterson" wrote:

Just a suggestion...

I'd spend time rearranging the data so that the quantities go in one column.

Then I'd apply Data|Filter|autofilter to that column with the quantities.

You can filter to show non-blanks (or greater than 0) and print that.

It may be easier to implement, too. (And less work that has to be thrown
away???)

MarkT wrote:

Hello,

I am trying to create a packing list of items needed for an order. I
currently have a spreadsheet that lists all available items in various sizes
etc. Instead of printing off this entire sheet, which would go accross two
pages and down two pages I would like a form created which will list(print)
only the items selected.

I am running Excel 2003, XP Pro. The current worksheet contains columns for
the item description a blank column for the quantity that the Sales Rep
enters in, along with the cost and selling price of that item. There are
over 200 items. The items are fairly static, in other words, we don't add
parts too frequently, so it's not real dynamic. The set of four columns then
repeats across the width of the sheet with additonal items and information.

I'm looking for a printout of only those items where the Sales Rep has
entered in the quantity. I assume this can be done via a macro, however I am
unsure of the exact langauage to use - something along the lines of:

Look at a list of specific cells for the quantity entered.

If Quantity is non blank, copy description (which will be in a cell directly
to the left of the quantity cell) and the quantity to another worksheet
within the same workbook.

Go to next quantity cell location and repeat until done.

The result would then be my "packing list" which would list only those
items, along with the quantity, on a separate worksheet so that I could print
that off.

The quantity cell locations are in more than one column, and would almost
have to be manually entered in to be checked for quantity in the macro.

At some point in the future I am planning on re-vamping this form so that
the quantities are all in the same column, I assume this task would be much
easier if this were the case.

Thanks in advance for all your help and suggestions!

Mark

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi Dave,

I did not have any luck with your suggested code regarding the creation of a
"packing list" for our various components using an existing order form.

I have decided to entirely reformat the order form so that the various
quantities needed will all be the same column (Column B). I believe you had
suggested this to me.

So, this is the new scenero:

Column A will contain the various parts and pieces needed for the sales rep
to select. Column B will be left blank, and this is where the sales person
will enter in the quantity. Column C will have the current cost.

I am trying to figure out how I could have only those items appear on a new
worksheet where the sales rep has entered in a quantity.

There may be several hundred rows with the various parts, but only those
with a quantity entered in column B are those that I would like to have
brought over to a "packing list". The part description (column A) and the
quantity needed (column B).

Do you have any suggestions as to how I may accomplish this?

Thank you very much for your help in the past and on this current problem of
mine.

Mark

"Dave Peterson" wrote:

Remember to backup often and save before you experiment.

It's easy to close without saving after you've screwed things up, er, modified
in the wrong direction.

MarkT wrote:

Dave,

I'm new to VBA, so it looks like I will be breaking open my new book from
Walkenhbach - "Excel 2003 Power Programming with VBA" tonight!

I will give your code a try.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim RptWks As Worksheet
Dim iRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim FirstCol As Long
Dim HowManyQtyCols As Long
Dim HowManyColsPerGroup As Long
Dim DestCell As Range
Dim qtyCol As Long

Set CurWks = Worksheets("sheet1")
Set RptWks = Worksheets("sheet2")

'first cell to get data on the report worksheet
Set DestCell = RptWks.Range("A11")

With CurWks
'first column with quantities
FirstCol = 1 'column A

'first row with qty
FirstRow = 6
'use the second column (desc) to get the last row for all columns?
LastRow = .Cells(.Rows.Count, FirstCol + 1).End(xlUp).Row

'4 qty columns
HowManyQtyCols = 4

'how many columns per group
HowManyColsPerGroup = 4

For iCol = 1 To HowManyQtyCols
qtyCol = FirstCol + (HowManyColsPerGroup * (iCol - 1))
For iRow = FirstRow To LastRow
If .Cells(iRow, qtyCol).Value = 0 Then
'do nothing
Else
DestCell.Resize(1, HowManyColsPerGroup).Value _
= .Cells(iRow, qtyCol) _
.Resize(1, HowManyColsPerGroup).Value

'get ready for the next non-zero one
Set DestCell = DestCell.Offset(1, 0)
End If
Next iRow
Next iCol
End With

End Sub


MarkT wrote:

Hi Dave,

Thanks for your input, I certainly understand where you are coming from as
that was what I wanted to do as well. However, this spreadsheet is the basis
for about eight other attached worksheets that gather information on pricing,
remanufacturing and inventory. This is a project of mine to do some time in
2007, however I was looking to see if there was some way to do this without
revamping the current layout.

Thanks again for your thoughts!

Mark

"Dave Peterson" wrote:

Just a suggestion...

I'd spend time rearranging the data so that the quantities go in one column.

Then I'd apply Data|Filter|autofilter to that column with the quantities.

You can filter to show non-blanks (or greater than 0) and print that.

It may be easier to implement, too. (And less work that has to be thrown
away???)

MarkT wrote:

Hello,

I am trying to create a packing list of items needed for an order. I
currently have a spreadsheet that lists all available items in various sizes
etc. Instead of printing off this entire sheet, which would go accross two
pages and down two pages I would like a form created which will list(print)
only the items selected.

I am running Excel 2003, XP Pro. The current worksheet contains columns for
the item description a blank column for the quantity that the Sales Rep
enters in, along with the cost and selling price of that item. There are
over 200 items. The items are fairly static, in other words, we don't add
parts too frequently, so it's not real dynamic. The set of four columns then
repeats across the width of the sheet with additonal items and information.

I'm looking for a printout of only those items where the Sales Rep has
entered in the quantity. I assume this can be done via a macro, however I am
unsure of the exact langauage to use - something along the lines of:

Look at a list of specific cells for the quantity entered.

If Quantity is non blank, copy description (which will be in a cell directly
to the left of the quantity cell) and the quantity to another worksheet
within the same workbook.

Go to next quantity cell location and repeat until done.

The result would then be my "packing list" which would list only those
items, along with the quantity, on a separate worksheet so that I could print
that off.

The quantity cell locations are in more than one column, and would almost
have to be manually entered in to be checked for quantity in the macro.

At some point in the future I am planning on re-vamping this form so that
the quantities are all in the same column, I assume this task would be much
easier if this were the case.

Thanks in advance for all your help and suggestions!

Mark

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Don't copy the data to a new sheet.

Apply Data|filter|autofilter and show the quantities that are non-zero.

Then print those visible rows.

MarkT wrote:

Hi Dave,

I did not have any luck with your suggested code regarding the creation of a
"packing list" for our various components using an existing order form.

I have decided to entirely reformat the order form so that the various
quantities needed will all be the same column (Column B). I believe you had
suggested this to me.

So, this is the new scenero:

Column A will contain the various parts and pieces needed for the sales rep
to select. Column B will be left blank, and this is where the sales person
will enter in the quantity. Column C will have the current cost.

I am trying to figure out how I could have only those items appear on a new
worksheet where the sales rep has entered in a quantity.

There may be several hundred rows with the various parts, but only those
with a quantity entered in column B are those that I would like to have
brought over to a "packing list". The part description (column A) and the
quantity needed (column B).

Do you have any suggestions as to how I may accomplish this?

Thank you very much for your help in the past and on this current problem of
mine.

Mark

"Dave Peterson" wrote:

Remember to backup often and save before you experiment.

It's easy to close without saving after you've screwed things up, er, modified
in the wrong direction.

MarkT wrote:

Dave,

I'm new to VBA, so it looks like I will be breaking open my new book from
Walkenhbach - "Excel 2003 Power Programming with VBA" tonight!

I will give your code a try.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim RptWks As Worksheet
Dim iRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim FirstCol As Long
Dim HowManyQtyCols As Long
Dim HowManyColsPerGroup As Long
Dim DestCell As Range
Dim qtyCol As Long

Set CurWks = Worksheets("sheet1")
Set RptWks = Worksheets("sheet2")

'first cell to get data on the report worksheet
Set DestCell = RptWks.Range("A11")

With CurWks
'first column with quantities
FirstCol = 1 'column A

'first row with qty
FirstRow = 6
'use the second column (desc) to get the last row for all columns?
LastRow = .Cells(.Rows.Count, FirstCol + 1).End(xlUp).Row

'4 qty columns
HowManyQtyCols = 4

'how many columns per group
HowManyColsPerGroup = 4

For iCol = 1 To HowManyQtyCols
qtyCol = FirstCol + (HowManyColsPerGroup * (iCol - 1))
For iRow = FirstRow To LastRow
If .Cells(iRow, qtyCol).Value = 0 Then
'do nothing
Else
DestCell.Resize(1, HowManyColsPerGroup).Value _
= .Cells(iRow, qtyCol) _
.Resize(1, HowManyColsPerGroup).Value

'get ready for the next non-zero one
Set DestCell = DestCell.Offset(1, 0)
End If
Next iRow
Next iCol
End With

End Sub


MarkT wrote:

Hi Dave,

Thanks for your input, I certainly understand where you are coming from as
that was what I wanted to do as well. However, this spreadsheet is the basis
for about eight other attached worksheets that gather information on pricing,
remanufacturing and inventory. This is a project of mine to do some time in
2007, however I was looking to see if there was some way to do this without
revamping the current layout.

Thanks again for your thoughts!

Mark

"Dave Peterson" wrote:

Just a suggestion...

I'd spend time rearranging the data so that the quantities go in one column.

Then I'd apply Data|Filter|autofilter to that column with the quantities.

You can filter to show non-blanks (or greater than 0) and print that.

It may be easier to implement, too. (And less work that has to be thrown
away???)

MarkT wrote:

Hello,

I am trying to create a packing list of items needed for an order. I
currently have a spreadsheet that lists all available items in various sizes
etc. Instead of printing off this entire sheet, which would go accross two
pages and down two pages I would like a form created which will list(print)
only the items selected.

I am running Excel 2003, XP Pro. The current worksheet contains columns for
the item description a blank column for the quantity that the Sales Rep
enters in, along with the cost and selling price of that item. There are
over 200 items. The items are fairly static, in other words, we don't add
parts too frequently, so it's not real dynamic. The set of four columns then
repeats across the width of the sheet with additonal items and information.

I'm looking for a printout of only those items where the Sales Rep has
entered in the quantity. I assume this can be done via a macro, however I am
unsure of the exact langauage to use - something along the lines of:

Look at a list of specific cells for the quantity entered.

If Quantity is non blank, copy description (which will be in a cell directly
to the left of the quantity cell) and the quantity to another worksheet
within the same workbook.

Go to next quantity cell location and repeat until done.

The result would then be my "packing list" which would list only those
items, along with the quantity, on a separate worksheet so that I could print
that off.

The quantity cell locations are in more than one column, and would almost
have to be manually entered in to be checked for quantity in the macro.

At some point in the future I am planning on re-vamping this form so that
the quantities are all in the same column, I assume this task would be much
easier if this were the case.

Thanks in advance for all your help and suggestions!

Mark

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi Dave,

I did think of the autofilter function, however, there will be several Sales
Reps, all with very limited knowledge of Excel using this workbook. I was
thinking of making this task as easy as possible by creating some sort of
macro and assigning it to a button so all they would need to do is enter in
the quantities, hit the button, then print off the resulting sheet.

I know I could simply record a macro to do the above using the same sheet;
however, the resulting packing list is also going to be the basis for quoting
the job as well. I thought that I would have a certain number of lines fixed
on the next worksheet (the worksheet where the resulting packing list data is
created) with all of the quoting formulas on it, then have the "packing list"
data fall in below.

"Dave Peterson" wrote:

Don't copy the data to a new sheet.

Apply Data|filter|autofilter and show the quantities that are non-zero.

Then print those visible rows.

MarkT wrote:

Hi Dave,

I did not have any luck with your suggested code regarding the creation of a
"packing list" for our various components using an existing order form.

I have decided to entirely reformat the order form so that the various
quantities needed will all be the same column (Column B). I believe you had
suggested this to me.

So, this is the new scenero:

Column A will contain the various parts and pieces needed for the sales rep
to select. Column B will be left blank, and this is where the sales person
will enter in the quantity. Column C will have the current cost.

I am trying to figure out how I could have only those items appear on a new
worksheet where the sales rep has entered in a quantity.

There may be several hundred rows with the various parts, but only those
with a quantity entered in column B are those that I would like to have
brought over to a "packing list". The part description (column A) and the
quantity needed (column B).

Do you have any suggestions as to how I may accomplish this?

Thank you very much for your help in the past and on this current problem of
mine.

Mark

"Dave Peterson" wrote:

Remember to backup often and save before you experiment.

It's easy to close without saving after you've screwed things up, er, modified
in the wrong direction.

MarkT wrote:

Dave,

I'm new to VBA, so it looks like I will be breaking open my new book from
Walkenhbach - "Excel 2003 Power Programming with VBA" tonight!

I will give your code a try.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim RptWks As Worksheet
Dim iRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim FirstCol As Long
Dim HowManyQtyCols As Long
Dim HowManyColsPerGroup As Long
Dim DestCell As Range
Dim qtyCol As Long

Set CurWks = Worksheets("sheet1")
Set RptWks = Worksheets("sheet2")

'first cell to get data on the report worksheet
Set DestCell = RptWks.Range("A11")

With CurWks
'first column with quantities
FirstCol = 1 'column A

'first row with qty
FirstRow = 6
'use the second column (desc) to get the last row for all columns?
LastRow = .Cells(.Rows.Count, FirstCol + 1).End(xlUp).Row

'4 qty columns
HowManyQtyCols = 4

'how many columns per group
HowManyColsPerGroup = 4

For iCol = 1 To HowManyQtyCols
qtyCol = FirstCol + (HowManyColsPerGroup * (iCol - 1))
For iRow = FirstRow To LastRow
If .Cells(iRow, qtyCol).Value = 0 Then
'do nothing
Else
DestCell.Resize(1, HowManyColsPerGroup).Value _
= .Cells(iRow, qtyCol) _
.Resize(1, HowManyColsPerGroup).Value

'get ready for the next non-zero one
Set DestCell = DestCell.Offset(1, 0)
End If
Next iRow
Next iCol
End With

End Sub


MarkT wrote:

Hi Dave,

Thanks for your input, I certainly understand where you are coming from as
that was what I wanted to do as well. However, this spreadsheet is the basis
for about eight other attached worksheets that gather information on pricing,
remanufacturing and inventory. This is a project of mine to do some time in
2007, however I was looking to see if there was some way to do this without
revamping the current layout.

Thanks again for your thoughts!

Mark

"Dave Peterson" wrote:

Just a suggestion...

I'd spend time rearranging the data so that the quantities go in one column.

Then I'd apply Data|Filter|autofilter to that column with the quantities.

You can filter to show non-blanks (or greater than 0) and print that.

It may be easier to implement, too. (And less work that has to be thrown
away???)

MarkT wrote:

Hello,

I am trying to create a packing list of items needed for an order. I
currently have a spreadsheet that lists all available items in various sizes
etc. Instead of printing off this entire sheet, which would go accross two
pages and down two pages I would like a form created which will list(print)
only the items selected.

I am running Excel 2003, XP Pro. The current worksheet contains columns for
the item description a blank column for the quantity that the Sales Rep
enters in, along with the cost and selling price of that item. There are
over 200 items. The items are fairly static, in other words, we don't add
parts too frequently, so it's not real dynamic. The set of four columns then
repeats across the width of the sheet with additonal items and information.

I'm looking for a printout of only those items where the Sales Rep has
entered in the quantity. I assume this can be done via a macro, however I am
unsure of the exact langauage to use - something along the lines of:

Look at a list of specific cells for the quantity entered.

If Quantity is non blank, copy description (which will be in a cell directly
to the left of the quantity cell) and the quantity to another worksheet
within the same workbook.

Go to next quantity cell location and repeat until done.

The result would then be my "packing list" which would list only those
items, along with the quantity, on a separate worksheet so that I could print
that off.

The quantity cell locations are in more than one column, and would almost
have to be manually entered in to be checked for quantity in the macro.

At some point in the future I am planning on re-vamping this form so that
the quantities are all in the same column, I assume this task would be much
easier if this were the case.

Thanks in advance for all your help and suggestions!

Mark

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I think you'll find that using data|filter|autofilter will be easier than the
macro.

Maybe you could record a macro that filters to show only the non-zero values,
prints and then shows all the data. You could record a macro when you do it
manually and you'll find the code is pretty much finished. Then assign that
code to a button (from the Forms toolbar) placed on the worksheet.

Alternatively, you could record a macro when you do the same thing, but
copy|paste to another sheet--but that seems like overkill to me.

===
As an aside. I think I'd explain how to use data|filter|autofilter. Not only
will the users find a reason to use it in this worksheet, they may find that it
makes working with other worksheets/workbooks much easier.



MarkT wrote:

Hi Dave,

I did think of the autofilter function, however, there will be several Sales
Reps, all with very limited knowledge of Excel using this workbook. I was
thinking of making this task as easy as possible by creating some sort of
macro and assigning it to a button so all they would need to do is enter in
the quantities, hit the button, then print off the resulting sheet.

I know I could simply record a macro to do the above using the same sheet;
however, the resulting packing list is also going to be the basis for quoting
the job as well. I thought that I would have a certain number of lines fixed
on the next worksheet (the worksheet where the resulting packing list data is
created) with all of the quoting formulas on it, then have the "packing list"
data fall in below.

"Dave Peterson" wrote:

Don't copy the data to a new sheet.

Apply Data|filter|autofilter and show the quantities that are non-zero.

Then print those visible rows.

MarkT wrote:

Hi Dave,

I did not have any luck with your suggested code regarding the creation of a
"packing list" for our various components using an existing order form.

I have decided to entirely reformat the order form so that the various
quantities needed will all be the same column (Column B). I believe you had
suggested this to me.

So, this is the new scenero:

Column A will contain the various parts and pieces needed for the sales rep
to select. Column B will be left blank, and this is where the sales person
will enter in the quantity. Column C will have the current cost.

I am trying to figure out how I could have only those items appear on a new
worksheet where the sales rep has entered in a quantity.

There may be several hundred rows with the various parts, but only those
with a quantity entered in column B are those that I would like to have
brought over to a "packing list". The part description (column A) and the
quantity needed (column B).

Do you have any suggestions as to how I may accomplish this?

Thank you very much for your help in the past and on this current problem of
mine.

Mark

"Dave Peterson" wrote:

Remember to backup often and save before you experiment.

It's easy to close without saving after you've screwed things up, er, modified
in the wrong direction.

MarkT wrote:

Dave,

I'm new to VBA, so it looks like I will be breaking open my new book from
Walkenhbach - "Excel 2003 Power Programming with VBA" tonight!

I will give your code a try.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim RptWks As Worksheet
Dim iRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim FirstCol As Long
Dim HowManyQtyCols As Long
Dim HowManyColsPerGroup As Long
Dim DestCell As Range
Dim qtyCol As Long

Set CurWks = Worksheets("sheet1")
Set RptWks = Worksheets("sheet2")

'first cell to get data on the report worksheet
Set DestCell = RptWks.Range("A11")

With CurWks
'first column with quantities
FirstCol = 1 'column A

'first row with qty
FirstRow = 6
'use the second column (desc) to get the last row for all columns?
LastRow = .Cells(.Rows.Count, FirstCol + 1).End(xlUp).Row

'4 qty columns
HowManyQtyCols = 4

'how many columns per group
HowManyColsPerGroup = 4

For iCol = 1 To HowManyQtyCols
qtyCol = FirstCol + (HowManyColsPerGroup * (iCol - 1))
For iRow = FirstRow To LastRow
If .Cells(iRow, qtyCol).Value = 0 Then
'do nothing
Else
DestCell.Resize(1, HowManyColsPerGroup).Value _
= .Cells(iRow, qtyCol) _
.Resize(1, HowManyColsPerGroup).Value

'get ready for the next non-zero one
Set DestCell = DestCell.Offset(1, 0)
End If
Next iRow
Next iCol
End With

End Sub


MarkT wrote:

Hi Dave,

Thanks for your input, I certainly understand where you are coming from as
that was what I wanted to do as well. However, this spreadsheet is the basis
for about eight other attached worksheets that gather information on pricing,
remanufacturing and inventory. This is a project of mine to do some time in
2007, however I was looking to see if there was some way to do this without
revamping the current layout.

Thanks again for your thoughts!

Mark

"Dave Peterson" wrote:

Just a suggestion...

I'd spend time rearranging the data so that the quantities go in one column.

Then I'd apply Data|Filter|autofilter to that column with the quantities.

You can filter to show non-blanks (or greater than 0) and print that.

It may be easier to implement, too. (And less work that has to be thrown
away???)

MarkT wrote:

Hello,

I am trying to create a packing list of items needed for an order. I
currently have a spreadsheet that lists all available items in various sizes
etc. Instead of printing off this entire sheet, which would go accross two
pages and down two pages I would like a form created which will list(print)
only the items selected.

I am running Excel 2003, XP Pro. The current worksheet contains columns for
the item description a blank column for the quantity that the Sales Rep
enters in, along with the cost and selling price of that item. There are
over 200 items. The items are fairly static, in other words, we don't add
parts too frequently, so it's not real dynamic. The set of four columns then
repeats across the width of the sheet with additonal items and information.

I'm looking for a printout of only those items where the Sales Rep has
entered in the quantity. I assume this can be done via a macro, however I am
unsure of the exact langauage to use - something along the lines of:

Look at a list of specific cells for the quantity entered.

If Quantity is non blank, copy description (which will be in a cell directly
to the left of the quantity cell) and the quantity to another worksheet
within the same workbook.

Go to next quantity cell location and repeat until done.

The result would then be my "packing list" which would list only those
items, along with the quantity, on a separate worksheet so that I could print
that off.

The quantity cell locations are in more than one column, and would almost
have to be manually entered in to be checked for quantity in the macro.

At some point in the future I am planning on re-vamping this form so that
the quantities are all in the same column, I assume this task would be much
easier if this were the case.

Thanks in advance for all your help and suggestions!

Mark

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson