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]

:dunno: 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.