CODEWITHSUNDEEP
vbaexcel
Shenanigator
Shenanigator

Reputation: 1066

Excel VBA PasteSpecial

Set copySheet = Worksheets("Metrics")
Set pasteSheet = Worksheets("Metrics")

copySheet.Range("A1:J5").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(2, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True

I have written this chunk of VBA code and it does mostly what I want it to do, but what I cannot figure out is how to apply a second pastespecial to get the formats from the Copied section to the newly pasted section. I tried adding a second .PasteSpecial xlPasteFormats under the first pastespecial line, but the macro fails at that point.

Any ideas about how to get the format to the newly pasted section?

Upvotes: 1

Views: 878

Answers (1)

Shenanigator
Shenanigator

Reputation: 1066

It's not a pretty answer, but 

pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(-4, 0).PasteSpecial xlPasteFormats

That does end up working. I put that right below the first pasteSheet line.

Now I have to insert the month of the year in the first cell after pasting... that will be fun.

Upvotes: 1

Related Questions