Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HAVE ONE REPORT SHEET WITH LINKS FROM VARIOUS SHEETS. ON COPYING THIS LINKED SHEET FOR EXPORTING IT TO THE OFFICE GET ERRORS IN CELLS. HOW TO RESOLVE THIS?
DEAR ALL
I have about 16 sheets of various calculations. In some sheets i have links from the remaining sheets which sort of sums up my reports for the day to the company. I did face lots of problems like, i cant copy and paste becos cells are not identical or merged cells problems etc. At present i have been selecting cells-copy-workbook add- paste. After some learning through this forum, i put offset formulas so that i dont need to write it in VB. Now the problem is when i see the sheet which i export t has #value messages. I will be proceeding on leave from this ship-- so pls help me quickly in advising me how to 1. Best way to copy from a sheet which has links, merged cells to a new sheet. 2. Is there a way to code it in VB, where once i select the cells, I would like to copy values, formats everything except the links and formulas. Eagerly awaiting n pls help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HAVE ONE REPORT SHEET WITH LINKS FROM VARIOUS SHEETS. ON COPYING THIS LINKED SHEET FOR EXPORTING IT TO THE OFFICE GET ERRORS IN CELLS. HOW TO RESOLVE THIS?
Two things.
Get rid of the merged cells, they are more trouble than they are worth. What is the formula that shows #VALUE, and what should it show? Oh 3 things actually. You got rid of the upper-case message, how about doing the same with the subject line? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "CAPTGNVR" wrote in message ps.com... DEAR ALL I have about 16 sheets of various calculations. In some sheets i have links from the remaining sheets which sort of sums up my reports for the day to the company. I did face lots of problems like, i cant copy and paste becos cells are not identical or merged cells problems etc. At present i have been selecting cells-copy-workbook add- paste. After some learning through this forum, i put offset formulas so that i dont need to write it in VB. Now the problem is when i see the sheet which i export t has #value messages. I will be proceeding on leave from this ship-- so pls help me quickly in advising me how to 1. Best way to copy from a sheet which has links, merged cells to a new sheet. 2. Is there a way to code it in VB, where once i select the cells, I would like to copy values, formats everything except the links and formulas. Eagerly awaiting n pls help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
HAVE ONE REPORT SHEET WITH LINKS FROM VARIOUS SHEETS. ON COPYING THIS LINKED SHEET FOR EXPORTING IT TO THE OFFICE GET ERRORS IN CELLS. HOW TO RESOLVE THIS?
On Feb 17, 7:17 pm, "Bob Phillips" wrote:
Two things. Get rid of the merged cells, they are more trouble than they are worth. What is the formula that shows #VALUE, and what should it show? Oh 3 things actually. You got rid of the upper-case message, how about doing the same with the subject line? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "CAPTGNVR" wrote in message ps.com... DEAR ALL I have about 16 sheets of various calculations. In some sheets i have links from the remaining sheets which sort of sums up my reports for the day to the company. I did face lots of problems like, i cant copy and paste becos cells are not identical or merged cells problems etc. At present i have been selecting cells-copy-workbook add- paste. After some learning through this forum, i put offset formulas so that i dont need to write it in VB. Now the problem is when i see the sheet which i export t has #value messages. I will be proceeding on leave from this ship-- so pls help me quickly in advising me how to 1. Best way to copy from a sheet which has links, merged cells to a new sheet. 2. Is there a way to code it in VB, where once i select the cells, I would like to copy values, formats everything except the links and formulas. Eagerly awaiting n pls help. D/Bob, thnks ur quick response. For senti reasons-- u gave me the headstart for my first post and from there I have really learning at reg intervals. It was all fine - this copying to another sheet etc with all the links. Then instead of fixing it thro VB, i changed it all to excel offset function and got the values. Felt nice for a day that it was much simpler than writing the VB code every time we change the group of certain cargo tanks. So with urs and Mr. Dave's advice i figured out how to put the sum of the groups in VB. Mainly I figured out the number of tanks in each group and store it in cells. I recall this numbers and used it with offset to get values from a ref cell. The next day when I have to send the report - i found all the cells are with #value error bcos the new sheet refers to the main sheet and with the offset absent from that sheet, it gives the #value error. Ah btw, that caps in the subject is for a quick glance over the scanning for my post. Can u also tell me how to go to say 20th page in the discusion forum instead of going one by one. Pls do not mind the sub in caps-rqst. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
HAVE ONE REPORT SHEET WITH LINKS FROM VARIOUS SHEETS. ON COPYING THIS LINKED SHEET FOR EXPORTING IT TO THE OFFICE GET ERRORS IN CELLS. HOW TO RESOLVE THIS?
I am not too sure why the offset is causing the #VALUE problem.Is it just a
matter having to update the formula when the new sheet is added? As to seeing your posts, I don't know what you are using to see these posts, but I use Outlook Express, and I can flag posts there, and sort the flagged posts to the top, thereby being able to easily follow targetted threads. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "CAPTGNVR" wrote in message oups.com... On Feb 17, 7:17 pm, "Bob Phillips" wrote: Two things. Get rid of the merged cells, they are more trouble than they are worth. What is the formula that shows #VALUE, and what should it show? Oh 3 things actually. You got rid of the upper-case message, how about doing the same with the subject line? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "CAPTGNVR" wrote in message ps.com... DEAR ALL I have about 16 sheets of various calculations. In some sheets i have links from the remaining sheets which sort of sums up my reports for the day to the company. I did face lots of problems like, i cant copy and paste becos cells are not identical or merged cells problems etc. At present i have been selecting cells-copy-workbook add- paste. After some learning through this forum, i put offset formulas so that i dont need to write it in VB. Now the problem is when i see the sheet which i export t has #value messages. I will be proceeding on leave from this ship-- so pls help me quickly in advising me how to 1. Best way to copy from a sheet which has links, merged cells to a new sheet. 2. Is there a way to code it in VB, where once i select the cells, I would like to copy values, formats everything except the links and formulas. Eagerly awaiting n pls help. D/Bob, thnks ur quick response. For senti reasons-- u gave me the headstart for my first post and from there I have really learning at reg intervals. It was all fine - this copying to another sheet etc with all the links. Then instead of fixing it thro VB, i changed it all to excel offset function and got the values. Felt nice for a day that it was much simpler than writing the VB code every time we change the group of certain cargo tanks. So with urs and Mr. Dave's advice i figured out how to put the sum of the groups in VB. Mainly I figured out the number of tanks in each group and store it in cells. I recall this numbers and used it with offset to get values from a ref cell. The next day when I have to send the report - i found all the cells are with #value error bcos the new sheet refers to the main sheet and with the offset absent from that sheet, it gives the #value error. Ah btw, that caps in the subject is for a quick glance over the scanning for my post. Can u also tell me how to go to say 20th page in the discusion forum instead of going one by one. Pls do not mind the sub in caps-rqst. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
HAVE ONE REPORT SHEET WITH LINKS FROM VARIOUS SHEETS. ON COPYING THIS LINKED SHEET FOR EXPORTING IT TO THE OFFICE GET ERRORS IN CELLS. HOW TO RESOLVE THIS?
When I am copying an entire sheet, with formulas, links, and merged cells,
to another workbook or new workbook, I use this code: Dim Wb1 As Workbook 'Originating workbook Dim Wb2 As Workbook ''Copy to" workbook Set Wb1 = ActiveWorkbook Set Wb2 = Workbooks.Open("C:\Your file to open.xls") ' Or 'Workbooks.Add 'Set Wb2 = ActiveWorkbook Wb1.Sheets("Sheet1").Copy _ after:=Wb2.Sheets(Wb2.Sheets.Count) Cells.Copy Cells.PasteSpecial xlPasteValues This retains all of the original formatting but sets all formulas and links to values. When I am copying portions of the sheet to another sheet: Wb1. Activate Range("So & So").Copy Wb2.Activate Range("So & So").PasteSpecial xlPasteValues Range("So & So").PasteSpecial xlPasteFormats Mr. Phillips is totally correct about Merged cells. They are a pain. They require additional code and sometimes can not be set at all due to the formatting of the worksheet being pasted to. Every case of Merged cells has to be tested. If you feel you need merged cells, the best approach to Copy is by using xlPasteValues & xlPasteFormats. Regards, Alan "CAPTGNVR" wrote in message ps.com... DEAR ALL I have about 16 sheets of various calculations. In some sheets i have links from the remaining sheets which sort of sums up my reports for the day to the company. I did face lots of problems like, i cant copy and paste becos cells are not identical or merged cells problems etc. At present i have been selecting cells-copy-workbook add- paste. After some learning through this forum, i put offset formulas so that i dont need to write it in VB. Now the problem is when i see the sheet which i export t has #value messages. I will be proceeding on leave from this ship-- so pls help me quickly in advising me how to 1. Best way to copy from a sheet which has links, merged cells to a new sheet. 2. Is there a way to code it in VB, where once i select the cells, I would like to copy values, formats everything except the links and formulas. Eagerly awaiting n pls help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying text on sheet 1 to corresponding cells on sheets 2 to 5 | New Users to Excel | |||
sorting master sheet messes up cells in other sheets linked to it | Excel Worksheet Functions | |||
sorting master sheet messes up cells in other sheets linked to it | Excel Worksheet Functions | |||
Report: Copying pagebreaks of a reference sheet to other sheets | Excel Programming | |||
Errors when copying a sheet | Excel Programming |