VBA Copy and PasteSpecial Table Values and Formatting

Question

I am still new with VBA but decided using VBa to streamline a Monthly report i am working on for our payroll would be the most efficient way, I wish to export a range I3:U2270 into a new sheet named from a cell in the summary sheet. everything works fine except formatting;

If I use xlPasteAll the formatting is perfect but it shows values as formulas.

If I use:

.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.PasteSpecial Paste:=xlPasteColumnWidths
.PasteSpecial Paste:=xlPasteFormats

The data and cell widths are perfect but because some data is in Tables it doesn't bring the formatting across.

Any ideas would be appreciated!

Sub MonthlySummaryExport()

    Dim NewSheetName As String
    Dim Newsheet As Object
    On Error Resume Next
    NewSheetName = Worksheets("Monthly Summary").Range("T1")
    If NewSheetName = "" Then Exit Sub
        Set Newsheet = Sheets(NewSheetName)
        If Not Newsheet Is Nothing Then
            MsgBox "Sheet cannot be created as there is already a worksheet with the same name" & " " & (NewSheetName)
            Exit Sub
            End If
            Sheets.Add(, Sheets(Sheets.Count)).Name = NewSheetName

    'Copy and PasteSpecial a between worksheets
    Worksheets("Monthly Summary").Range("I3:U2270").Copy

    With Worksheets(NewSheetName).Range("A1")
    .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    .PasteSpecial Paste:=xlPasteColumnWidths
    .PasteSpecial Paste:=xlPasteFormats
    End With

    'Disable marching ants around copied range
    Application.CutCopyMode = False
End Sub

Any suggestions would be great! Thanks

Answer 1

Copy everything including formulas, then change formulas to values in the copy

'Copy and PasteSpecial a between worksheets
    dim dest as range
    set dest = Worksheets(NewSheetName).Range("A1")
    Worksheets("Monthly Summary").Range("I3:U2270").Copy dest
    dest.currentregion.copy
    dest.pastespecial xlPasteValues