PDA

View Full Version : [SOLVED] Help with paste special



peterwmartin
09-15-2017, 05:16 AM
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
09-15-2017, 06:09 AM
: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
09-15-2017, 08:28 AM
Thanks so much that seems to have worked better.