Excel Vba copy and paste

Question

While copying data from one Excel sheet to another workbook I have to paste data with the same format because each cell has interior color. I will also delete the data from the original sheet which I copied from. Is it possible to keep the source formatting?

Workbooks.Add
Set Outbook1 = ActiveWorkbook
OutBook.Activate
OutBook.Sheets("Sheet6").Select
Lines = ActiveSheet.Range("A65536").End(xlUp).Row
Range("A1:N" & Lines).Select
Selection.Copy

Outbook1.Activate
Outbook1.Sheets("Sheet1").Range("A1").PasteSpecial (xlPasteAll)

Community · Accepted Answer · 2017-05-23 11:53:05Z

1

You can keep the source formatting (as long as it's not conditional formatting), using the PasteSpecial xlPasteAll command.

Recommendation: It's always better if you stay from Activate , Select and Selection, instead use referenced Workbooks and Sheets.

(for more details go to How to avoid using Select in Excel VBA macros )

Code

Option Explicit

Sub CopyBetweenSheets()

Dim Outbook1        As Workbook
Dim OutBook         As Workbook
Dim Lines           As Long

Set OutBook = ThisWorkbook
Set Outbook1 = Workbooks.Add

With OutBook
    With .Sheets("Sheet6")
        ' find last row in Column A in "Sheet6"
        Lines = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("A1:N" & Lines).Copy
    End With
End With

' paste to "A1" in "Sheet1" in the new workbook
Outbook1.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteAll

End Sub

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Dec 11, 2016 at 9:44

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

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

3 Comments

yuvaraj Over a year ago

still color of each row is not copied to new workbook

Shai Rado Over a year ago

@yuvaraj what color? Are u referring to conditional formatting?

Shai Rado Over a year ago

@yuvaraj it's ok :) and thanks for accepting my answer

Collectives™ on Stack Overflow

Excel Vba copy and paste

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