xlPasteValues and xlPasteFormats at the same time

user_7130016 0
Viewed 17757

I am copying a range and then pasting it's values and formats to another range:

ws5.Range("F3:N" & xCell).SpecialCells(xlCellTypeVisible).Copy
    
ws16.Cells(Rows.Count, "B").End(xlUp).Offset(4, 0).PasteSpecial xlPasteValues
ws16.Cells(Rows.Count, "B").End(xlUp).Offset(4, 0).PasteSpecial xlPasteFormats

Is it possible to do these two paste actions in one action without using With?

I would like to increase the speed of my macro and that kind of small reduction might help me.

Is there something like .PasteSpecial xlPasteValues and xlPasteFormats?

I found link but even this answer is using With, which is useless for me.

paste copy-paste vba excel
4 Answers

You can use it this way:

NewSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
NewSheet.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Place the two commands one after each other. First, you have to paste all, then you paste again, but only values and number formats.

user_143461580
user_54059670

You can paste the content several times to get what you need, like 

Cells(1, 1).PasteSpecial Paste:=xlPasteAll
Cells(1, 1).PasteSpecial Paste:=xlPasteValues
user_43754380
Related