Copy changes value

J

Jos Vens

Hi,

I have a strange problem.

I have a cell where I put in the formula =Now(). I want then to export that
sheet with fixed values, so first I copy and paste values over the sheet,
then uses the command copy. The strange thing is that the value of the date
(which is changed into a fixed value of type date) is change to somewhere in
the year 2000. What can be the reason?

Thanks Jos Vens

Here is a bit of my code (everything works fine except if there's a date in
the spreadsheet)

Set sTemp = ActiveSheet
sTemp.Name = "mx" & sLijst.Name

Range(sTemp.Cells(1, 1), sTemp.Cells(vRows, vCols)).Copy
Range(sTemp.Cells(1, 1), sTemp.Cells(vRows, vCols)).PasteSpecial
Paste:=xlValues

Range(sTemp.Cells(1, vCols + 1), sTemp.Cells(1100, 255)).Clear
Range(sTemp.Cells(vRows + 1, 1), sTemp.Cells(1100, 255)).Clear

sTemp.[A1].Select

sTemp.Copy 'This line changes the date into another date in the past

Set wbFile = ActiveWorkbook
Set sOutput = ActiveSheet

If (vProtect) Then
sOutput.Protect Password:=vPswDsg, DrawingObjects:=True,
Contents:=True, Scenarios:=True
wbFile.Protect Password:=vPswDsg, Structure:=True, Windows:=False
End If

sOutput.PageSetup.PrintArea = Range(sOutput.Cells(1, 1),
sOutput.Cells(vRows, vCols)).Address
sOutput.Name = vLKLas

wbFile.SaveAs Filename:=vPath & vLKLas & " Lijst.xls"
wbFile.Close

sTemp.Delete
 
K

Ken Wright

Is the date almost exactly 4 years off in every case? In that case you need to
make sure both workbooks are using the same date options - Tools / Options /
Calculation / Uncheck 'Use 1904 date system'. (Assuming that' the way round you
want it - For an explanation of the two date systems:-

From Chip Pearson's site (Which you should bookmark and explore)

http://cpearson.com/excel/datetime.htm

The Two Date Systems Of Excel (Just a snippet from that link)

In the section above, it was said that the date portion of a serial date-time
represents the number of days since 1900-Jan-0. This is true, but Excel can
also treat the date portion of a serial date as the number of days since
1904-Jan-0. This mode is, called the 1904-mode or 1904-system, is used for
compatibility with the Macintosh system. The date mode applies to all dates
within a workbook. You cannot mix and match modes within a workbook. Unless
absolutely required, you should never use the 1904-system. If you change
systems after you've entered some dates in a workbook, these dates will appear
to be off 4 years (because the serial dates haven't changed, only their display
representation has). Moreover, linking workbooks with different date systems
will cause problems.

You might also want to look at Chip's pages on dates in VBA

http://cpearson.com/excel/DateTimeVBA.htm

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :)
----------------------------------------------------------------------------



Jos Vens said:
Hi,

I have a strange problem.

I have a cell where I put in the formula =Now(). I want then to export that
sheet with fixed values, so first I copy and paste values over the sheet,
then uses the command copy. The strange thing is that the value of the date
(which is changed into a fixed value of type date) is change to somewhere in
the year 2000. What can be the reason?

Thanks Jos Vens

Here is a bit of my code (everything works fine except if there's a date in
the spreadsheet)

Set sTemp = ActiveSheet
sTemp.Name = "mx" & sLijst.Name

Range(sTemp.Cells(1, 1), sTemp.Cells(vRows, vCols)).Copy
Range(sTemp.Cells(1, 1), sTemp.Cells(vRows, vCols)).PasteSpecial
Paste:=xlValues

Range(sTemp.Cells(1, vCols + 1), sTemp.Cells(1100, 255)).Clear
Range(sTemp.Cells(vRows + 1, 1), sTemp.Cells(1100, 255)).Clear

sTemp.[A1].Select

sTemp.Copy 'This line changes the date into another date in the past

Set wbFile = ActiveWorkbook
Set sOutput = ActiveSheet

If (vProtect) Then
sOutput.Protect Password:=vPswDsg, DrawingObjects:=True,
Contents:=True, Scenarios:=True
wbFile.Protect Password:=vPswDsg, Structure:=True, Windows:=False
End If

sOutput.PageSetup.PrintArea = Range(sOutput.Cells(1, 1),
sOutput.Cells(vRows, vCols)).Address
sOutput.Name = vLKLas

wbFile.SaveAs Filename:=vPath & vLKLas & " Lijst.xls"
wbFile.Close

sTemp.Delete
Click to expand...
 
J

Jos Vens

Thanks Ken,

this was exactly the solution!

Jos

Ken Wright said:
Is the date almost exactly 4 years off in every case? In that case you need to
make sure both workbooks are using the same date options - Tools / Options /
Calculation / Uncheck 'Use 1904 date system'. (Assuming that' the way round you
want it - For an explanation of the two date systems:-

From Chip Pearson's site (Which you should bookmark and explore)

http://cpearson.com/excel/datetime.htm

The Two Date Systems Of Excel (Just a snippet from that link)

In the section above, it was said that the date portion of a serial date-time
represents the number of days since 1900-Jan-0. This is true, but Excel can
also treat the date portion of a serial date as the number of days since
1904-Jan-0. This mode is, called the 1904-mode or 1904-system, is used for
compatibility with the Macintosh system. The date mode applies to all dates
within a workbook. You cannot mix and match modes within a workbook. Unless
absolutely required, you should never use the 1904-system. If you change
systems after you've entered some dates in a workbook, these dates will appear
to be off 4 years (because the serial dates haven't changed, only their display
representation has). Moreover, linking workbooks with different date systems
will cause problems.

You might also want to look at Chip's pages on dates in VBA

http://cpearson.com/excel/DateTimeVBA.htm

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

-------------------------------------------------------------------------- --
It's easier to beg forgiveness than ask permission :)
-------------------------------------------------------------------------- --



Jos Vens said:
Hi,

I have a strange problem.

I have a cell where I put in the formula =Now(). I want then to export that
sheet with fixed values, so first I copy and paste values over the sheet,
then uses the command copy. The strange thing is that the value of the date
(which is changed into a fixed value of type date) is change to somewhere in
the year 2000. What can be the reason?

Thanks Jos Vens

Here is a bit of my code (everything works fine except if there's a date in
the spreadsheet)

Set sTemp = ActiveSheet
sTemp.Name = "mx" & sLijst.Name

Range(sTemp.Cells(1, 1), sTemp.Cells(vRows, vCols)).Copy
Range(sTemp.Cells(1, 1), sTemp.Cells(vRows, vCols)).PasteSpecial
Paste:=xlValues

Range(sTemp.Cells(1, vCols + 1), sTemp.Cells(1100, 255)).Clear
Range(sTemp.Cells(vRows + 1, 1), sTemp.Cells(1100, 255)).Clear

sTemp.[A1].Select

sTemp.Copy 'This line changes the date into another date in the past

Set wbFile = ActiveWorkbook
Set sOutput = ActiveSheet

If (vProtect) Then
sOutput.Protect Password:=vPswDsg, DrawingObjects:=True,
Contents:=True, Scenarios:=True
wbFile.Protect Password:=vPswDsg, Structure:=True, Windows:=False
End If

sOutput.PageSetup.PrintArea = Range(sOutput.Cells(1, 1),
sOutput.Cells(vRows, vCols)).Address
sOutput.Name = vLKLas

wbFile.SaveAs Filename:=vPath & vLKLas & " Lijst.xls"
wbFile.Close

sTemp.Delete
Click to expand...
Click to expand...
 
K

Ken Wright

My pleasure - thanks for letting me know :)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :)
----------------------------------------------------------------------------



Jos Vens said:
Thanks Ken,

this was exactly the solution!

Jos

Ken Wright said:
Is the date almost exactly 4 years off in every case? In that case you need to
make sure both workbooks are using the same date options - Tools / Options /
Calculation / Uncheck 'Use 1904 date system'. (Assuming that' the way round you
want it - For an explanation of the two date systems:-

From Chip Pearson's site (Which you should bookmark and explore)

http://cpearson.com/excel/datetime.htm

The Two Date Systems Of Excel (Just a snippet from that link)

In the section above, it was said that the date portion of a serial date-time
represents the number of days since 1900-Jan-0. This is true, but Excel can
also treat the date portion of a serial date as the number of days since
1904-Jan-0. This mode is, called the 1904-mode or 1904-system, is used for
compatibility with the Macintosh system. The date mode applies to all dates
within a workbook. You cannot mix and match modes within a workbook. Unless
absolutely required, you should never use the 1904-system. If you change
systems after you've entered some dates in a workbook, these dates will appear
to be off 4 years (because the serial dates haven't changed, only their display
representation has). Moreover, linking workbooks with different date systems
will cause problems.

You might also want to look at Chip's pages on dates in VBA

http://cpearson.com/excel/DateTimeVBA.htm

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

-------------------------------------------------------------------------- --
It's easier to beg forgiveness than ask permission :)
-------------------------------------------------------------------------- --



Jos Vens said:
Hi,

I have a strange problem.

I have a cell where I put in the formula =Now(). I want then to export that
sheet with fixed values, so first I copy and paste values over the sheet,
then uses the command copy. The strange thing is that the value of the date
(which is changed into a fixed value of type date) is change to somewhere in
the year 2000. What can be the reason?

Thanks Jos Vens

Here is a bit of my code (everything works fine except if there's a date in
the spreadsheet)

Set sTemp = ActiveSheet
sTemp.Name = "mx" & sLijst.Name

Range(sTemp.Cells(1, 1), sTemp.Cells(vRows, vCols)).Copy
Range(sTemp.Cells(1, 1), sTemp.Cells(vRows, vCols)).PasteSpecial
Paste:=xlValues

Range(sTemp.Cells(1, vCols + 1), sTemp.Cells(1100, 255)).Clear
Range(sTemp.Cells(vRows + 1, 1), sTemp.Cells(1100, 255)).Clear

sTemp.[A1].Select

sTemp.Copy 'This line changes the date into another date in the past

Set wbFile = ActiveWorkbook
Set sOutput = ActiveSheet

If (vProtect) Then
sOutput.Protect Password:=vPswDsg, DrawingObjects:=True,
Contents:=True, Scenarios:=True
wbFile.Protect Password:=vPswDsg, Structure:=True, Windows:=False
End If

sOutput.PageSetup.PrintArea = Range(sOutput.Cells(1, 1),
sOutput.Cells(vRows, vCols)).Address
sOutput.Name = vLKLas

wbFile.SaveAs Filename:=vPath & vLKLas & " Lijst.xls"
wbFile.Close

sTemp.Delete
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy and Paste Special #Value Error 3
VBA group the number in the Column and Copy to the new worksheet. 0
URGENT!!! Problem with row data being truncated in a copy worksheet sub 1

Top