copy from 1 sheet to another

metfast

New Member
Joined
Aug 26, 2017
Messages
18
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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,176
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Aug 26, 2017
Messages
18
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,176
Office Version
  1. 2016
Platform
  1. Windows
Are you performing VLOOKUP between sheets in same workbook or different workbook? I cannot understand why asking for file number(?)
 

metfast

New Member
Joined
Aug 26, 2017
Messages
18

ADVERTISEMENT

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 GoodsNo ReqRRP inc GST
LMLandscape Mix1$55.44
PMPotting Mix1$97.02
SmSheep Manure1$61.60
JmJungle Mulch1$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 Items4
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,176
Office Version
  1. 2016
Platform
  1. Windows
Book1.xlsx
ABCDE
1ID#Description of GoodsNo ReqRRP inc GST
2LMLandscape Mix1$55.44
3PMPotting Mix1$97.02
4SmSheep Manure1$61.60
5JmJungle Mulch1$61.60
60-0$0.00
70-0$0.00
8Total Items4
9Total cost$275.66
10Inc GST of$25.06
Sheet1


Book1.xlsx
ABCD
1ID#Description of GoodsRRP inc GST
2LMLandscape Mix$55.44
3PMPotting Mix$97.02
4SmSheep Manure$61.60
5JmJungle Mulch$61.60
6
Sheet2
Cell Formulas
RangeFormula
B2:B5B2=VLOOKUP(A2,Sheet1!A2:E7,2,0)
D2:D5D2=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
 
Solution

metfast

New Member
Joined
Aug 26, 2017
Messages
18
Book1.xlsx
ABCDE
1ID#Description of GoodsNo ReqRRP inc GST
2LMLandscape Mix1$55.44
3PMPotting Mix1$97.02
4SmSheep Manure1$61.60
5JmJungle Mulch1$61.60
60-0$0.00
70-0$0.00
8Total Items4
9Total cost$275.66
10Inc GST of$25.06
Sheet1


Book1.xlsx
ABCD
1ID#Description of GoodsRRP inc GST
2LMLandscape Mix$55.44
3PMPotting Mix$97.02
4SmSheep Manure$61.60
5JmJungle Mulch$61.60
6
Sheet2
Cell Formulas
RangeFormula
B2:B5B2=VLOOKUP(A2,Sheet1!A2:E7,2,0)
D2:D5D2=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
ABCDE
1ID#Description of GoodsNo ReqRRP inc GST
2LMLandscape Mix1$55.44
3PMPotting Mix1$97.02
4SmSheep Manure1$61.60
5JmJungle Mulch1$61.60
60-0$0.00
70-0$0.00
8Total Items4
9Total cost$275.66
10Inc GST of$25.06
Sheet1


Book1.xlsx
ABCD
1ID#Description of GoodsRRP inc GST
2LMLandscape Mix$55.44
3PMPotting Mix$97.02
4SmSheep Manure$61.60
5JmJungle Mulch$61.60
6
Sheet2
Cell Formulas
RangeFormula
B2:B5B2=VLOOKUP(A2,Sheet1!A2:E7,2,0)
D2:D5D2=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

New Member
Joined
Aug 26, 2017
Messages
18
Hi mate thanks the problem solved got the answer from this
it's so simple, i thought i wood share it.
Regards GE
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,550
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top