Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a variable cell within a worksheet using a macro.
Good day all,
I have looked through many of the postings on various questions, but because of limited time I thought it best to post my problem directly. I have a need to move data from a set location in one workbook, to a variable location within another workbook called: Order Tracking Log. My situation: I generate a sequential Master Tracking Number (MAS) for either sales or service items. I then provide numerous quotes (QT), acknowledge (ACK) the orders received against those quotes, and then invoice (INV) once shipped (sometimes multiple invoices against a single order). Although my QT, ACK, and INV numbers are sequential, and are tied to a MAS #, they rarely if ever match that MAS number. My issue: I am currently manually coping data from a range of cells in a single row within one workbook, then manually moving that data to a range of cells within my €śOrder Tracking Log€ť workbook. I need to automate this process. My format: The rows are in descending sequential order by MAS # (Column B): A B C D I N O 1 Customer MAS RMA QT ACK PK INV 2 Customer A 003/10 002/10 003/10 002/10 003/10 005/10 3 Customer Z 002/10 001/10 002/10 004/10 001/10 001/10 4 Customer A 001/10 001/10 003/10 002/10 004/10 5 Customer D 156/09 208/09 001/10 004/10 002/10 6 Customer D 156/09 208/09 001/10 005/10 003/10 (PK) = Packing Slip (RMA) = Return Material Authorization Following each category (QT, ACK, & INV) is a breakdown of that categories data: D E F G H 1 QT Amount Part Cost Labor Shipping 2 003/10 =SUM(F2:H2) $100.00 $100.00 $300.00 3 002/10 =SUM(F3:H3) $150.00 $200.00 $150.00 My requirement: To take the data destined for columns F, G, & H from another workbook, locate the row identified by a given MAS #, and then paste the data in columns F, G, & H of the €śOrder Tracking Log€ť. This process will be randomly repeated for all three categories; QT, ACK, & INV. My skill set: I am currently able to callout the €śOrder Tracking Log€ť workbook, and then locate the correct worksheet within that workbook, but I am unable to locate the variable row & column I require to paste the data. I can move data to a fixed point within a workbook, but my need is to take the data from a random MAS # and its corresponding QT, ACK or INV, and paste that data in the correct location. I am sorry for the lengthy description, but I thought more detail is better in this situation. Thank you for your help, J.R. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a variable cell within a worksheet using a macro.
and you tried the VLOOKUP() function?
"J.R." wrote: Good day all, I have looked through many of the postings on various questions, but because of limited time I thought it best to post my problem directly. I have a need to move data from a set location in one workbook, to a variable location within another workbook called: Order Tracking Log. My situation: I generate a sequential Master Tracking Number (MAS) for either sales or service items. I then provide numerous quotes (QT), acknowledge (ACK) the orders received against those quotes, and then invoice (INV) once shipped (sometimes multiple invoices against a single order). Although my QT, ACK, and INV numbers are sequential, and are tied to a MAS #, they rarely if ever match that MAS number. My issue: I am currently manually coping data from a range of cells in a single row within one workbook, then manually moving that data to a range of cells within my €śOrder Tracking Log€ť workbook. I need to automate this process. My format: The rows are in descending sequential order by MAS # (Column B): A B C D I N O 1 Customer MAS RMA QT ACK PK INV 2 Customer A 003/10 002/10 003/10 002/10 003/10 005/10 3 Customer Z 002/10 001/10 002/10 004/10 001/10 001/10 4 Customer A 001/10 001/10 003/10 002/10 004/10 5 Customer D 156/09 208/09 001/10 004/10 002/10 6 Customer D 156/09 208/09 001/10 005/10 003/10 (PK) = Packing Slip (RMA) = Return Material Authorization Following each category (QT, ACK, & INV) is a breakdown of that categories data: D E F G H 1 QT Amount Part Cost Labor Shipping 2 003/10 =SUM(F2:H2) $100.00 $100.00 $300.00 3 002/10 =SUM(F3:H3) $150.00 $200.00 $150.00 My requirement: To take the data destined for columns F, G, & H from another workbook, locate the row identified by a given MAS #, and then paste the data in columns F, G, & H of the €śOrder Tracking Log€ť. This process will be randomly repeated for all three categories; QT, ACK, & INV. My skill set: I am currently able to callout the €śOrder Tracking Log€ť workbook, and then locate the correct worksheet within that workbook, but I am unable to locate the variable row & column I require to paste the data. I can move data to a fixed point within a workbook, but my need is to take the data from a random MAS # and its corresponding QT, ACK or INV, and paste that data in the correct location. I am sorry for the lengthy description, but I thought more detail is better in this situation. Thank you for your help, J.R. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a variable cell within a worksheet using a macro.
Hi Patrick,
Sorry for the delay in answering. I have tried the VLOOKUP() function, but I am trying to perform my tack within a macro, and I was unaware that you could perform the VLOOKUP() function within a macro. "Patrick Molloy" wrote: and you tried the VLOOKUP() function? "J.R." wrote: Good day all, I have looked through many of the postings on various questions, but because of limited time I thought it best to post my problem directly. I have a need to move data from a set location in one workbook, to a variable location within another workbook called: Order Tracking Log. My situation: I generate a sequential Master Tracking Number (MAS) for either sales or service items. I then provide numerous quotes (QT), acknowledge (ACK) the orders received against those quotes, and then invoice (INV) once shipped (sometimes multiple invoices against a single order). Although my QT, ACK, and INV numbers are sequential, and are tied to a MAS #, they rarely if ever match that MAS number. My issue: I am currently manually coping data from a range of cells in a single row within one workbook, then manually moving that data to a range of cells within my €śOrder Tracking Log€ť workbook. I need to automate this process. My format: The rows are in descending sequential order by MAS # (Column B): A B C D I N O 1 Customer MAS RMA QT ACK PK INV 2 Customer A 003/10 002/10 003/10 002/10 003/10 005/10 3 Customer Z 002/10 001/10 002/10 004/10 001/10 001/10 4 Customer A 001/10 001/10 003/10 002/10 004/10 5 Customer D 156/09 208/09 001/10 004/10 002/10 6 Customer D 156/09 208/09 001/10 005/10 003/10 (PK) = Packing Slip (RMA) = Return Material Authorization Following each category (QT, ACK, & INV) is a breakdown of that categories data: D E F G H 1 QT Amount Part Cost Labor Shipping 2 003/10 =SUM(F2:H2) $100.00 $100.00 $300.00 3 002/10 =SUM(F3:H3) $150.00 $200.00 $150.00 My requirement: To take the data destined for columns F, G, & H from another workbook, locate the row identified by a given MAS #, and then paste the data in columns F, G, & H of the €śOrder Tracking Log€ť. This process will be randomly repeated for all three categories; QT, ACK, & INV. My skill set: I am currently able to callout the €śOrder Tracking Log€ť workbook, and then locate the correct worksheet within that workbook, but I am unable to locate the variable row & column I require to paste the data. I can move data to a fixed point within a workbook, but my need is to take the data from a random MAS # and its corresponding QT, ACK or INV, and paste that data in the correct location. I am sorry for the lengthy description, but I thought more detail is better in this situation. Thank you for your help, J.R. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to name a worksheet with a date variable? | Excel Programming | |||
Variable worksheet name (tab) linked to cell in same worksheet | Excel Worksheet Functions | |||
Running a variable macro when any value is entered into a variable cell | Excel Programming | |||
Find variable cell text in another range | Excel Programming | |||
Define worksheet as variable, then call in macro | Excel Programming |