CODEWITHSUNDEEP
excelvbapaste
RMF
RMF

Reputation: 9

How to prevent Excel VBA paste special values from changing data type from number/date to text

I am encountering a problem where Excel VBA paste special values is changing the data type to text when it carries out the paste values operation, which then breaks downstream formulas that expect to see a number/date instead of text.

At a high level, the process I have is the following:

  1. starting with functional worksheet, duplicate and rename it;

  2. perform operations on the newly created sheet including copy, paste special values;

  3. formulas that depend on the pasted data are now broken because data type has been changed to text.

The code that is doing the copy paste special values looks like this:

Workbooks("myFile.xlsm").Sheets(pageFocus).Range(refreshCopyRange).Copy

Workbooks("myFile.xlsm").Sheets(pageFocus).Range(pasteRange).PasteSpecial (xlPasteValues)

Is there some kind of modifier or override to PasteSpecial (xlPasteValues) that will stop Excel VBA from changing the data type?

Many thanks in advance for any help!

Upvotes: 0

Views: 4184

Answers (2)

Bookeater
Bookeater

Reputation: 474

You need to do a second paste command for formatting:

Workbooks("myFile.xlsm").Sheets(pageFocus).Range(pasteRange).PasteSpecial xlPasteValues
Workbooks("myFile.xlsm").Sheets(pageFocus).Range(pasteRange).PasteSpecial xlPasteFormats

Here is a sample:
https://social.msdn.microsoft.com/Forums/office/en-US/b593e52c-910c-4d24-b738-65878fe8a50d/how-to-copypaste-range-values-and-formats?forum=exceldev

Upvotes: 2

datatoo
datatoo

Reputation: 2049

You can use this 

.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False

Upvotes: 1

Related Questions