CODEWITHSUNDEEP
vbaexcel
Navron
Navron

Reputation: 145

Excel Pastespecial VBA

I am attempting to copy the contents of a certain workbook to another. However, I indent to keep the source formatting. However, my code produces application/object not defined errors. Any help would be much appreciated.

Public Sub CommandButton1_Click()
Worksheets("Sheet1").Range("A2:D349").Copy Destination:=Worksheets("Sheet6").Range("A2")
Worksheets("Sheet1").Range("A2:D349").PasteSpecial Paste:=xlPasteFormats
End Sub

Upvotes: 1

Views: 6428

Answers (1)

Rory
Rory

Reputation: 34035

There's nothing on the clipboard when you specify a destination, so you have to separate the steps:

Public Sub CommandButton1_Click()
Worksheets("Sheet1").Range("A2:D349").Copy 
Worksheets("Sheet6").Range("A2").PasteSpecial xlPasteAll
Worksheets("Sheet1").Range("A2:D349").PasteSpecial Paste:=xlPasteFormats
End Sub

Upvotes: 2

Related Questions