Re: Paste results not as expected

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thanks for your response.

I did what you said and the original data returned TRUE for all of the cells.
For the pasted cells doing it manually it returned TRUE for 1 date and FALSE
for another.

I am using UK dates and I noticed that a date such as 30/10/2006 did not
paste correctly using the macro but a date such as 01/11/2006 did paste
correctly.

Hope that helps to clarify things.


"Dave Peterson" wrote:

First, I wouldn't give up on just using one instance of excel and trying to get
that to work correctly.

I can't recall a time when I copied a date and pasted and it didn't stay a date.

Are you sure your original data is really dates?

If you put
=isnumber(a1)
(if a1 is one of the cells copied)
do you get true or false?

If you do the same thing in the receiving worksheet/workbook, do you get true or
false?

The only time I've ever had trouble with dates is when I copied from a different
workbook and that workbook used a different starting date (1904 vs. 1900, on
tools|options|calculation tab).

But the values get pasted as dates, just not the dates I want!

Barbar wrote:

First post, so please be gentle on me!!

I have a workbook with data exported from another DB which contain a mixture
of dates number and times. These are mixed within the same columns.

If I try to copy and paste to another worksheet or workbook within the same
instance of excel the dates are not pasted as dates (even if I use paste
special and select values only). The dates are formatted to the left by
default.

I have got around this by opening another copy of excel and then using paste
special which gives me an entirley different dialog box and then I select
text or csv as the paste special format. Thsi gives me dates as dates
(formatted to the right by default)

So far so good, but now I want to automate the copy ansd paste special

I am using

ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
This gives the original error of the dates not being true dates. Now they
are formatted to the left by default and any calulation using the dates fails.

The macro was recorded and does not work despite the fact that the original
action that created the macro worked.

Any suggestions?

--

Dave Peterson

.

Relevant Pages

  • Re: Formatting Cells
    ... There were .wk* files that were opened in excel and saved as .xls. ... new worksheet in a new workbook. ... My solution for the CR files was to use the sacrificial cells and format more ...
    (microsoft.public.excel)
  • Re: Formatting Cells
    ... Some of the cells may have been protected in the other program and Excel is ... The strange part is that many of the cells can be formatted perfectly. ... There are about 10 sheets in this workbook. ... I have a workbook that I would like to format ...
    (microsoft.public.excel)
  • Re: Paste With A Custom Format
    ... Out of 118 cells, about 45 are ... getting their figures from this external workbook. ... you cannot "format" text. ... I am passing a string of characters in the format like this: ...
    (microsoft.public.excel.programming)
  • Re: Problem Updating Links
    ... You can either open that other workbook, then do those steps--or browse for that ... command opens a file selection box for each cell, but never makes a change in ... Changing the format of a cell isn't enough. ... If you have lots of these cells to fix. ...
    (microsoft.public.excel.programming)
  • Using Conditional Formats
    ... I have a workbook that I am using to manage my travel with. ... What I would like to be able to do is format the cells in the calendar view ...
    (microsoft.public.excel.misc)