copy from 1 sheet to another

[metfast]

OK i will explain as best as possible, in sheet one i have 10 rows (a1,a2,a3,a4,a5,) that can look up stock description ect, i have used VLookup to produce this. now when i copy and try to paste to sheet 2 it just says N/A so i try to special past that works but only for the first line, im try to find an easy quick way to send the data so as to print a reciept.
A1 is where lookup code goes column b shows description, c shows price.

row1 A1 B1 C1
row2 RW Red Wood $50.00.
Ect to row 10 my vlookup formula reads =VLOOKUP(a2,A2:C10,2,False) ect i think you get it.
no all i want to do is send the data to another sheet or printer, now of course if go to print in the ribbon it will print the wholw page including colors this i don't want i would to send it to another sheet with my own receipt template, any ideas or suggestions would be appreciated.
Regards Ge Perth Western Australia.

 

[Zot]

My understanding is that you have VLOOKUP results in A1, A2, A3, A4, A5, etc. You want to transfer the result to Sheet2.
If you do Copy Paste manually it will show #N/A and if you Copy and use Paste Special Value you get the number. This is normal

If you put formula in Sheet2 like put = and click A1 in Sheet1, you get the value displayed and the formula in the cell would be =Sheet1!A1.
If you do not want it to be as formula, you just Copy whole range of results in Sheet2 and Paste as value.

This can be done easily without need of VBA code unless you want one

 

[metfast]

Hi ZOT sorry maybe i'm not understanding you reply, i have tried your suggestion and typed the formula in the receipts sheet as you said ( =inventory!c9.) that that is the sheet name and line number, when in the receipts sheet i put this formula where i want it to copied to and press enter it go's to my library (documents) asking for a file number.
Regards Ge

 

[Zot]

Are you performing VLOOKUP between sheets in same workbook or different workbook? I cannot understand why asking for file number(?)

 

[metfast]

Yes the two sheets are in the same workbook. I have done as you suggested I just copy and special paste (value) in the receipt sheet and all is working. The two sheets are called inventory sales & receipts. Below diagram might ex plane.
B C D E F
VLOOKUP starts at row B9 & go to row 20 i would like to just send this data to the Receipts sheet "cot at bottom"

ID#	Description of Goods		No Req	RRP inc GST
LM	Landscape Mix		1	$55.44
PM	Potting Mix		1	$97.02
Sm	Sheep Manure		1	$61.60
Jm	Jungle Mulch		1	$61.60
0	-		0	$0.00
0	-		0	$0.00
0	-		0	$0.00
0	-		0	$0.00
0	-		0	$0.00
0	-		0	$0.00
		Total Items	4
			Total cost	$275.66
			Inc GST of	$25.06

or maybe just create a button or word that i just click & the data goes to the receipt sheet i can the double check before printing. The receipt template is configured to except the inventory sales data as shown above. The copy special paste way is fine as it is only used about 10-15 times a day so i can cope, just trying to tweek the program.
Thanks for your help i nothing can be done " That's life i will live with it"
Regards Ge from Perth Western Australia (GMT + 8hrs )

 

[Zot]

Book1.xlsx
	A	B	C	D	E
1	ID#	Description of Goods		No Req	RRP inc GST
2	LM	Landscape Mix		1	$55.44
3	PM	Potting Mix		1	$97.02
4	Sm	Sheep Manure		1	$61.60
5	Jm	Jungle Mulch		1	$61.60
6	0	-		0	$0.00
7	0	-		0	$0.00
8			Total Items	4
9				Total cost	$275.66
10				Inc GST of	$25.06
Sheet1

Book1.xlsx
	A	B	C	D
1	ID#	Description of Goods		RRP inc GST
2	LM	Landscape Mix		$55.44
3	PM	Potting Mix		$97.02
4	Sm	Sheep Manure		$61.60
5	Jm	Jungle Mulch		$61.60
6
Sheet2

Cell Formulas
Range	Formula
B2:B5	B2	=VLOOKUP(A2,Sheet1!A2:E7,2,0)
D2:D5	D2	=VLOOKUP(A2,Sheet1!A2:E7,5,0)

I imagining that you have something like above. In this example, you have Sheet1 and Sheet2.

My understanding was that you copy value from Sheet1 and PasteSpecial to Sheet2 to get value. If just paste you get #N/ because you are copying formula. You can just put VLookUp in Sheet2 straight like above.

If that is not what you want, then you can just copy a range say for Sheet2:
Select B2~B5
Copy (or just press Ctrl+C)
Paste (or just press Ctrl+V)

This will remove all the formula and convert the cells to just real value.
Then you can just copy paste like normal

Let me know if this is not what you wanted

 

[metfast]

Book1.xlsx
	A	B	C	D	E
1	ID#	Description of Goods		No Req	RRP inc GST
2	LM	Landscape Mix		1	$55.44
3	PM	Potting Mix		1	$97.02
4	Sm	Sheep Manure		1	$61.60
5	Jm	Jungle Mulch		1	$61.60
6	0	-		0	$0.00
7	0	-		0	$0.00
8			Total Items	4
9				Total cost	$275.66
10				Inc GST of	$25.06
Sheet1

Book1.xlsx
	A	B	C	D
1	ID#	Description of Goods		RRP inc GST
2	LM	Landscape Mix		$55.44
3	PM	Potting Mix		$97.02
4	Sm	Sheep Manure		$61.60
5	Jm	Jungle Mulch		$61.60
6
Sheet2

Cell Formulas
Range	Formula
B2:B5	B2	=VLOOKUP(A2,Sheet1!A2:E7,2,0)
D2:D5	D2	=VLOOKUP(A2,Sheet1!A2:E7,5,0)

I imagining that you have something like above. In this example, you have Sheet1 and Sheet2.

My understanding was that you copy value from Sheet1 and PasteSpecial to Sheet2 to get value. If just paste you get #N/ because you are copying formula. You can just put VLookUp in Sheet2 straight like above.

If that is not what you want, then you can just copy a range say for Sheet2:
Select B2~B5
Copy (or just press Ctrl+C)
Paste (or just press Ctrl+V)

This will remove all the formula and convert the cells to just real value.
Then you can just copy paste like normal

Let me know if this is not what you wanted

Book1.xlsx
	A	B	C	D	E
1	ID#	Description of Goods		No Req	RRP inc GST
2	LM	Landscape Mix		1	$55.44
3	PM	Potting Mix		1	$97.02
4	Sm	Sheep Manure		1	$61.60
5	Jm	Jungle Mulch		1	$61.60
6	0	-		0	$0.00
7	0	-		0	$0.00
8			Total Items	4
9				Total cost	$275.66
10				Inc GST of	$25.06
Sheet1

Book1.xlsx
	A	B	C	D
1	ID#	Description of Goods		RRP inc GST
2	LM	Landscape Mix		$55.44
3	PM	Potting Mix		$97.02
4	Sm	Sheep Manure		$61.60
5	Jm	Jungle Mulch		$61.60
6
Sheet2

Cell Formulas
Range	Formula
B2:B5	B2	=VLOOKUP(A2,Sheet1!A2:E7,2,0)
D2:D5	D2	=VLOOKUP(A2,Sheet1!A2:E7,5,0)

I imagining that you have something like above. In this example, you have Sheet1 and Sheet2.

My understanding was that you copy value from Sheet1 and PasteSpecial to Sheet2 to get value. If just paste you get #N/ because you are copying formula. You can just put VLookUp in Sheet2 straight like above.

If that is not what you want, then you can just copy a range say for Sheet2:
Select B2~B5
Copy (or just press Ctrl+C)
Paste (or just press Ctrl+V)

This will remove all the formula and convert the cells to just real value.
Then you can just copy paste like normal

Let me know if this is not what you wanted

Hi Zot thanks for the reply i think i will stick with the copy & special paste (value), i tried the vlookup formula it just takes me to my folder in documents where the program is stored it even started changing the formula in the page i was trying to transfer the data from. The sheets in the program are not numbered as such but each sheet tab is named "inventry is sheet 15 from the first tab/sheet. Anyway as i said i can stick to the copy & special paste it's no big deal the receipts are only printed out 10 to 15 times a day, and no rush.

once again thanks for the help but i will percivere and do more research.
Regard Ge

 

[metfast]

Hi mate thanks the problem solved got the answer from this

it's so simple, i thought i wood share it.
Regards GE