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.

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.

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.

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.

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.