VBA - problems with pasting formats

[thorn91]

Hello

For some reason, my code only allows me to to use "Paste" or "Paste Special xlPasteValuesAndNumberFormats". In case I try to use "PasteSpecial xlPasteFormats", I'm getting 1004 error. I can't wrap my mind around what I might be doing wrong. Preferably I would need to paste both formats of the source file as well as values.

A piece of code causing the problem:

Private Sub SplitWorksheet(ByVal Category_Name As Variant)
    
    Dim wbTarget As Workbook
    Dim Target_Folder As String
    Dim column_name_to_use_2 As String
    Dim Prefix As String
    
    
    column_name_to_use_2 = wsHelper.Range("E1").Value
    
    Target_Folder = wsHelper.Range("F1").Value
    Prefix = wsHelper.Range("G1").Value
    
    Set wbTarget = Workbooks.Add
    
    With wsSource
        
        With .Range(.Cells(1, 1), .Cells(LastRow, LastColumn))
            .AutoFilter .Range(column_name_to_use_2).Column, Category_Name
            
            .Copy
            
            wbTarget.Worksheets(1).PasteSpecial xlPasteFormats
            wbTarget.Worksheets(1).PasteSpecial xlPasteValuesAndNumberFormats
            wbTarget.Worksheets(1).Name = Category_Name
            
            wbTarget.SaveAs Target_Folder & Prefix & "_" & Category_Name & ".xlsx", 51
            wbTarget.Close False
            
        End With
        
    End With
    
    Set wbTarget = Nothing


End Sub

If I remove "wbTarget.Worksheets(1).PasteSpecial xlPasteFormats" completely, it works just fine. But if I leave it like above, I get an error.

Would be very much grateful for any ideas.

 

[rlv01]

It is because you are using the Worksheet.PasteSpecial method instead of the Range.PasteSpecial method. Try using the range pastespecial method instead.

            wbTarget.Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteFormats
            wbTarget.Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

 

[thorn91]

It is because you are using the Worksheet.PasteSpecial method instead of the Range.PasteSpecial method. Try using the range pastespecial method instead.

            wbTarget.Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteFormats
            wbTarget.Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

This worked, thanks so much