Transfer Part Qty from one sheet to another.

[coolhit]

Hi

I need help with macro please can i ask for assistant. Have been trying now for few days and not getting any where.

I have attached file.

User enters Slot No in N13 (AM351001).
Cell N17 (code) & O17 (qty) is Vlookup AM351001 from sheet called Consumables In AM Rack.

What i want to do is user enter a slot in N13, Vlookup looks up the code & qty, then user enters qty which needs to be subtract in cell P17, O19=date and O20=name..

Once the data is filled in (green boxes), I want P17 qty to be subtract from the sheet called Consumables In AM Rack. And transfer the details which user has enter in sheet called Consumables Used From AM Rack.

example.
User enters in green box.
cell N13 = AM351001
cell P17 = 300
cell O19 = 18/08/2009
cell O20 = Hit.

When button is pressed.

1st cell P17 = 300 is subtracted from Consumables In AM Rack against AM351001.

2nd copy all the details which user enterd in sheet Consumables Used From AM Rack.
cell N17 = (lookupcode) in column B
cell P17 = 300 in column C
cell O19 = 18/08/2009 in column A
cell O20 = Hit. in column D

If you look at the file you will easly see it.

Please please can someone assist me.

Many thanks

[Richard Buttrey]

Hi,

First name your 4 cells

N17 = "Code"
Q17 = "Qty"
P19 = "Date"
P20 = "Authorised"

Now add the following procedure and attach it to your button

Please Login or Register  to view this content.

HTH

[coolhit]

Hi,

First name your 4 cells

N17 = "Code"
Q17 = "Qty"
P19 = "Date"
P20 = "Authorised"

Now add the following procedure and attach it to your button

Please Login or Register  to view this content.

HTH

Richard,

Mant Thanks work great, only one small question.

How do i clear the following cells once the macro has finished doing its job.

N13 = "Slot"
Q17 = "Qty"
P19 = "Date"
P20 = "Authorised"

Many thks once again.

[Palmetto]

Changes Made

Sheet3
Unmerged all data cells (avoid merged cells like the plague - use cetner across selection instead)

Moved look up formulas for Code and Qty to adjacent cells to perform look up once (for efficiency)

Revised lookup formula in cells for Code and Qty to reference the look up cells per above change.

VBA Code written for the command button

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

Just use the instructions

Please Login or Register  to view this content.

Rgds

[coolhit]

Hi,

Just use the instructions

Please Login or Register  to view this content.

Rgds

Hi Richard,

I copied your code which you adviced so it look like below.

Please Login or Register  to view this content.

I got

Run-time error '1004':
Cannot change part of a merged cell.

How do i get round this except for demerging. As it will spoil the layout look of the sheet.

This there any other way round.

Many thks for assistant.

[Palmetto]

Run-time error '1004':
Cannot change part of a merged cell.

How do i get round this except for demerging. As it will spoil the layout look of the sheet.

Apparently you haven't bothered to look at the solution I provided as it does away with merged cells AND retains the look.

This there any other way round

Yes. Don't use merged cells because they hinder more than they help. Use center across selection instead and adjust row heights rather than use blank rows - all of whch the solution I gave you uses.