Copy and paste format including colour of cells VBA Excel

Question

wbTarget.Sheets("Sheet1").Range("A1:W79").Value = wb.Sheets(wsSource.Name).Range("A1:W79").Value

I have this code which works pasting the values of wb.Sheets(wsSource.Name) to wbTarget sheets. However, it only pastes the value and not the format/color. How do I paste it including the fonts, color of cell.

Perhaps you could do what you want manually with the macro recorder turned on, and then look at the resulting code. — John Coleman
– John Coleman, Commented Mar 30, 2017 at 8:31
Do you want the equivalent of Ctrl-C - Ctrl-V - i.e. a full copy, or just the value, font and colour? — CLR
– CLR, Commented Mar 30, 2017 at 8:32

Shai Rado · Accepted Answer · 2017-03-30 08:31:00Z

10

You need to use Copy, and PasteSpecial xlValues and PasteSpecial xlFormats.

' Copy
wb.Sheets(wsSource.Name).Range("A1:W79").Copy
' Paste Special
wbTarget.Sheets("Sheet1").Range("A1:W79").PasteSpecial xlValues
wbTarget.Sheets("Sheet1").Range("A1:W79").PasteSpecial xlFormats

Read about Range.PasteSpecial here MSDN

answered Mar 30, 2017 at 8:31

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

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

3 Comments

Ninja Dude Over a year ago

Hi thanks for your help! Can I change the Range to be dynamic? So I hope to copy everything dynamically and not just A1:W79. How should I substitute it?

Shai Rado Over a year ago

@Desmond what is changing ? number of rows ? columns ? both ?

Ninja Dude Over a year ago

Both can change. So if the sheet contains data in A1:W79, I would copy and paste that. If a sheet contains data in C3:F7, I would copy that.

Collectives™ on Stack Overflow

Copy and paste format including colour of cells VBA Excel

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related