Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Packing List Macro
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Packing List Macro
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Packing List Macro
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Packing List Macro
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Packing List Macro
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Packing List Macro
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help (Uppercase multiple ranges?) | Excel Discussion (Misc queries) | |||
Help needed - creating invoice from packing list (both in excel) | Excel Discussion (Misc queries) | |||
Drop down list macro? | Excel Discussion (Misc queries) | |||
running a macro from a list | Excel Worksheet Functions | |||
Data Val list Excel 97 fire macro | Excel Discussion (Misc queries) |