How do I copy and paste values and formats to a new worksheet using VBA

Question

I am trying to copy the values and format from Sheet1 to Sheet2 but I am not having much luck. The code works fine but only pastes the Value

I tried using xlPasteFormats with xlPasteValues and I keep getting errors.

    Private Sub TransferButton_Click()
    Application.ScreenUpdating = False
      Dim copySheet As Worksheet
      Dim pasteSheet As Worksheet

      Set copySheet = Worksheets("Sheet1")
      Set pasteSheet = Worksheets("Sheet2")


  copySheet.Range("A1:G29").Copy

  pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial 
  Paste:=xlPasteAll
  pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial 
  Paste:=xlPasteColumnWidths

  Application.CutCopyMode = False
  Application.ScreenUpdating = True

      Range("D5").Value = Range("D5").Value + 1

        Range("A8").ClearContents
        Range("B8").ClearContents
        Range("C8").ClearContents
        Range("D8").ClearContents
        Range("A10").ClearContents
        Range("A13").ClearContents
        Range("B13").ClearContents
        Range("C13").ClearContents
        Range("D13").ClearContents
        Range("A15").ClearContents
        Range("B15").ClearContents
        Range("C15").ClearContents
        Range("C15").ClearContents
        Range("A17").ClearContents
        Range("C17").ClearContents
        Range("A20:D24").ClearContents
        Range("D25").ClearContents
        Range("A28").ClearContents
        Range("B28").ClearContents
        Range("C28").ClearContents

    End Sub

I expect the pasted range from sheet1 to be copied and pasted into sheet 2 with the same values, colours, fonts (Formats) e.c.t.

"I tried using xlPasteFormats with xlPasteValues" - can you show how you tried? — BigBen
@BigBen I tried doing it as seen below: copySheet.Range("A1:G29").Copy pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats — Whitena
@Whitena can you please add code by editing your question? It can be hard to parse otherwise — cybernetic.nomad

SofiaChalk SofiaChalk · Accepted Answer · 2019-10-28T15:32:30

  Dim copySheet As Worksheet
  Dim pasteSheet As Worksheet

  Set copySheet = Worksheets("Sheet1")
  Set pasteSheet = Worksheets("Sheet2")

  copySheet.Range("A1:G29").Copy
  With pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        .PasteSpecial xlPasteColumnWidths
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats
  End With

'You can assign the height of a row to different rows if xlPasteFormats does not do it for you
      height = copySheet.Range("A5").Height
      pasteSheet.Range("A1:G29").RowHeight = height

     End Sub

How do I copy and paste values and formats to a new worksheet using VBA

1 Answers