Taking Data from one tab, looking up another tab and pasting a row in

[ardykav]

Hi,

I ned to keep a record of stock sold each week in a sportstoreI have two tabs. One called "Weekly Update" and the other called "All Stock". All Columns are named and formatted the exact same on both tabs.

I need to do a Weekly update of stock sold on the first tab. This has 7 Columns and will be manually entered.

Column A: Code
Column B: Name
Column C: List Price
Column D: Colour
Column E: Warehouse
Column F: Sale Price
Column G: Status


Then at the end of the week i want to update the All Stock by pressing a button. So ideally the All stock tab will be looked up and if any of the records are on the weekly tab they will be replaced on the All stock tab.

Scenario would be as follows:
You would start off by having the following on the All stock Sheet
[TABLE="width: 500"]
<tbody>[TR]
[TD]Code
[/TD]
[TD]Name
[/TD]
[TD]List Price
[/TD]
[TD]Colour
[/TD]
[TD]Warehouse
[/TD]
[TD]Sale Price
[/TD]
[TD]Status
[/TD]
[/TR]
[TR]
[TD]451
[/TD]
[TD]Power Shoes
[/TD]
[TD]$25
[/TD]
[TD]Red
[/TD]
[TD]Fields
[/TD]
[TD]N/A
[/TD]
[TD]Available
[/TD]
[/TR]
</tbody>[/TABLE]


Then the weekly udpate is completed and has the following on it

[TABLE="width: 500"]
<tbody>[TR]
[TD]Code
[/TD]
[TD]Name
[/TD]
[TD]List Price
[/TD]
[TD]Colour
[/TD]
[TD]Warehouse
[/TD]
[TD]Sale Price
[/TD]
[TD]Status
[/TD]
[/TR]
[TR]
[TD]451
[/TD]
[TD]Power Shoes
[/TD]
[TD]$25
[/TD]
[TD]Red
[/TD]
[TD]Fields
[/TD]
[TD]$30
[/TD]
[TD]Sold
[/TD]
[/TR]
</tbody>[/TABLE]


Then the button is pressed and the All Stock tab is updated with the sold status Row

Any advice on the best way of going about this? Possibly a macro loop of some sort?


All tips greatly appreciated,

 

[ssingh75]

Hi,

I ned to keep a record of stock sold each week in a sportstoreI have two tabs. One called "Weekly Update" and the other called "All Stock". All Columns are named and formatted the exact same on both tabs.

I need to do a Weekly update of stock sold on the first tab. This has 7 Columns and will be manually entered.

Column A: Code
Column B: Name
Column C: List Price
Column D: Colour
Column E: Warehouse
Column F: Sale Price
Column G: Status


Then at the end of the week i want to update the All Stock by pressing a button. So ideally the All stock tab will be looked up and if any of the records are on the weekly tab they will be replaced on the All stock tab.

Scenario would be as follows:
You would start off by having the following on the All stock Sheet
[TABLE="width: 500"]
<tbody>[TR]
[TD]Code
[/TD]
[TD]Name
[/TD]
[TD]List Price
[/TD]
[TD]Colour
[/TD]
[TD]Warehouse
[/TD]
[TD]Sale Price
[/TD]
[TD]Status
[/TD]
[/TR]
[TR]
[TD]451
[/TD]
[TD]Power Shoes
[/TD]
[TD]$25
[/TD]
[TD]Red
[/TD]
[TD]Fields
[/TD]
[TD]N/A
[/TD]
[TD]Available
[/TD]
[/TR]
</tbody>[/TABLE]


Then the weekly udpate is completed and has the following on it

[TABLE="width: 500"]
<tbody>[TR]
[TD]Code
[/TD]
[TD]Name
[/TD]
[TD]List Price
[/TD]
[TD]Colour
[/TD]
[TD]Warehouse
[/TD]
[TD]Sale Price
[/TD]
[TD]Status
[/TD]
[/TR]
[TR]
[TD]451
[/TD]
[TD]Power Shoes
[/TD]
[TD]$25
[/TD]
[TD]Red
[/TD]
[TD]Fields
[/TD]
[TD]$30
[/TD]
[TD]Sold
[/TD]
[/TR]
</tbody>[/TABLE]


Then the button is pressed and the All Stock tab is updated with the sold status Row

Any advice on the best way of going about this? Possibly a macro loop of some sort?


All tips greatly appreciated,

So if your Status sold then it should populate the prize in other tab instead of N/A..right?

 

[ardykav]

So if your Status sold then it should populate the prize in other tab instead of N/A..right?

Thats correct, The end result would be that the all stock tab had the following row in it rather than what it was originally:

[TABLE="class: cms_table, width: 500"]

<tbody>
[TR]
[TD]451[/TD]
[TD]Power Shoes[/TD]
[TD]$25[/TD]
[TD]Red[/TD]
[TD]Fields[/TD]
[TD]$30[/TD]
[TD]Sold[/TD]
[/TR]

</tbody>
[/TABLE]