Help with paste special

[peterwmartin]

Hi all,
I am having problems with this bit of code, the paste bit could someone help please?
I would like it to keep the original formatting, I have had it working without the formatting

Sub CopyFilteredValuesToActiveWorkbook()

Dim wbSource As Workbook, wbDest As Workbook
Dim wsSource As Worksheet, wsDest As Worksheet
Dim rngSource As Range, rngDest As Range

Set wbSource = Workbooks.Open("C:\Users\peter\Desktop\excel test\test.xlsm", , True) 'Readonly = True

Set wsSource = wbSource.Worksheets("Sheet1")
wsSource.Range("A1:H3").AutoFilter Field:=2, Criteria1:="Line 6"

Set rngSource = wsSource.Range("A1:K10")
wsSource.Range("A1:f10").SpecialCells(xlCellTypeVisible).Copy

Set wbDest = ThisWorkbook
Set wsDest = wbDest.Worksheets("Sheet1")
'Set rngDest = wsDest.Range("A1:h20")

Range("A1").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False

wbSource.Close (False) 'Close without saving changes

End Sub

[SamT]

: Try this

Sub CopyFilteredValuesToActiveWorkbook() 
     
    Dim wbSource As Workbook, wbDest As Workbook 
    Dim wsSource As Worksheet, wsDest As Worksheet 
     
       Set wbDest = ThisWorkbook 
       Set wbSource = Workbooks.Open("C:\Users\peter\Desktop\excel test\test.xlsm", , True) 'Readonly = True
     
    Set wsSource = wbSource.Worksheets("Sheet1") 
    wsSource.Range("A1:H3").AutoFilter Field:=2, Criteria1:="Line 6" 
     
    wsSource.Range("A1:f10").SpecialCells(xlCellTypeVisible).Copy 
     
    wbDest.Worksheets("Sheet1").Range("A1").PasteSpecial _
 Paste:=xlPasteAllUsingSourceTheme
     
    wbSource.Close (False) 'Close without saving changes
     
End Sub

[peterwmartin]

Thanks so much that seems to have worked better.