paste special with formatting and without formula

[Dave_george]

Hello,

I have a Macro below that let's me grab data from other workbook. I need it to copy data without formula but needs to include the formatting.


Sub Copy_from_workbook()

Workbooks.Open "C:\Users\grg01\Desktop\perf.xlsx"

Workbooks("perf").Worksheets("Sheet4").Range("A1:F6").Copy _
Workbooks("dest").Worksheets("Figures").Range("B5:G10")

Workbooks("perf").Close SaveChanges:=True

End Sub

 

[Georgiboy]

Maybe:

Sub Copy_from_workbook()
    Dim wbPerf As Workbook, wbThis As Workbook
    
    Set wbPerf = Workbooks.Open("C:\Users\grg01\Desktop\perf.xlsx")
    Set wbThis = ThisWorkbook
    
    wbPerf.Worksheets("Sheet4").Range("A1:F6").Copy
    With wbThis.Sheets("Figures").Range("B5:G10")
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats
    End With
    
    Application.CutCopyMode = False
    wbPerf.Close True
End Sub

 

[Dave_george]

Maybe:

Sub Copy_from_workbook()
    Dim wbPerf As Workbook, wbThis As Workbook
   
    Set wbPerf = Workbooks.Open("C:\Users\grg01\Desktop\perf.xlsx")
    Set wbThis = ThisWorkbook
   
    wbPerf.Worksheets("Sheet4").Range("A1:F6").Copy
    With wbThis.Sheets("Figures").Range("B5:G10")
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats
    End With
   
    Application.CutCopyMode = False
    wbPerf.Close True
End Sub

Got a run time error on line .PasteSpecial xlPasteValues

 

[Dave_george]

Got a run time error on line .PasteSpecial xlPasteValues

 

[Joe4]

It sounds like you are using merged cells.
If at all possible, GET RID OF THEM!

Merged cells are one of the absolute worst features of Excel, and cause all sorts of issues for things like VBA and sorting.
As such, most experienced programmers will avoid them like the plague!

If you are just merging columns across single rows, it is much better to use the "Center Across Selection" formatting feature, which gives you all the same visual benefits of merged cells without all the issues.
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

 

[Dave_george]

It sounds like you are using merged cells.
If at all possible, GET RID OF THEM!

Merged cells are one of the absolute worst features of Excel, and cause all sorts of issues for things like VBA and sorting.
As such, most experienced programmers will avoid them like the plague!

If you are just merging columns across single rows, it is much better to use the "Center Across Selection" formatting feature, which gives you all the same visual benefits of merged cells without all the issues.
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

Thanks a bunch. Got rid of the cells and it worked like a charm. Also learnt something new.

 

[Dave_george]

Maybe:

Sub Copy_from_workbook()
    Dim wbPerf As Workbook, wbThis As Workbook
   
    Set wbPerf = Workbooks.Open("C:\Users\grg01\Desktop\perf.xlsx")
    Set wbThis = ThisWorkbook
   
    wbPerf.Worksheets("Sheet4").Range("A1:F6").Copy
    With wbThis.Sheets("Figures").Range("B5:G10")
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats
    End With
   
    Application.CutCopyMode = False
    wbPerf.Close True
End Sub

Thank you. It worked after removing merged cells from my workbook.

 

[Georgiboy]

Happy we could help, glad you got it sorted. I also agree with what was said about merged cells, they are a huge pain.