Error while pasting the data using Excel Vba

Question

I have 17 columns in my sheet 1 and when I am copying the data in sheet 2 only 13 columns are being pasted with the specified format sheet 2.

In that 17 columns I have date and time stamp. dates below 13 columns are pasted with yyyy-mm-dd hh:mm:ss format and dates which is beyond 13th column are pasted in (if this is the date 2018-03-01 03:00:00) it is pasted as 43160.125.

and the code I have used is

sht1.Cells.Copy
sht2.Range("A1").PasteSpecial xlPasteValues

P.S: I have used NumberFormat too but it's no use.

Copy and PasteSpecial is a 2- line command. You used 1) sht1.Cells.Copy 2) sht2.Range("A1").PasteSpecial xlPasteValues and 3) sht2.Range("A1").PasteSpecial xlPasteFormats and it didn't work ? — Shai Rado
– Shai Rado, Commented Apr 17, 2018 at 13:00

Siddharth Rout · Accepted Answer · 2018-04-17 13:00:07Z

1

Try this (Untested)

sht1.Cells.Copy
sht2.Range("A1").PasteSpecial xlPasteFormats
sht2.Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False

answered Apr 17, 2018 at 13:00

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ankita Roy Over a year ago

Thank you. it worked. Why only xlPasteValues cannot be used. Why do xlpasteFormats need to be used with that. I believe xlPasteValues pastes all the values. So, date and time isn't considered as a value. Is it a format?

Harassed Dad Over a year ago

Sort of: Dates are held as a long - the number of days since 1/1/1900. To see them in a conventional style such as "Jan 1 1900" you need a format. Similarly hours are held as decimal fractions of 24 hours - so 12 noon = 0.5

Siddharth Rout Over a year ago

@AnkitaRoy: Have a look at this cpearson.com/excel/datetime.htm

Collectives™ on Stack Overflow

Error while pasting the data using Excel Vba

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related