Copy paste values only

[TPFKAS]

Hi there,

In a peice of VBA code I am a using Copy-Paste with xlValues as follows:

Dim Sourcebook, Targetbook as Workbook
Dim Sourcesheet, Targetsheet as Worksheet
Dim Sourcerange, Targetrange as Range

........


    Sourcebook.Sourcesheet.SourceRange.Copy
        Targetbook.Targetsheet.TargetRange.PasteSpecial Paste:=xlPasteValues, SkipBlanks:=True

However, when I run this code, not only the values are pasted but also the formatting (borders, background and number formatting) are copied. Shouldn't xlPasteValues limit to values onlye or am I doing something wrong here?

 

[Fluff]

PasteValues, does what it says on the tin.
Can you provide you entire code, as from what you've posted & surprised it even runs.

 

[TPFKAS]

PasteValues, does what it says on the tin.
Can you provide you entire code, as from what you've posted & surprised it even runs.

The original code contained some extra lines to locate and define the sheets and ranges, so I stripped it down to the following:

Sub Import()


Dim Source, Target As Workbook
Dim app As New Excel.Application


Set Target = ActiveWorkbook


FileToOpen = Application.GetOpenFilename(Title:="Open the sourcefile", FileFilter:="Excel files (*.xlsx),")


If FileToOpen = False Then
    MsgBox "No file opened.", vbExclamation, "Message"
    Exit Sub
Else
    app.Visible = False
    Set Source = app.Workbooks.Add(FileToOpen)
    
    Source.Worksheets("Sheet1").Range("A1:C10").Copy
        Target.Worksheets("Sheet1").Range("A1:C10").PasteSpecial xlPasteValues
    
    app.DisplayAlerts = False
    Source.Close SaveChanges:=False
    app.Quit
    Set app = Nothing
    
End If
    
End Sub

I tested it by opening a Source workbook with a sheet called "Sheet 1" with some values in A1:C10 with different formats (background color, fonts etc.).
When running this code the values are copied to the target sheet including all formatting.
PS: I had some specific reasons to use a second instance of Excel to open the source file

 

[Fluff]

What if you try

Sub Import()


Dim Source As Workbook, Target As Workbook



Set Target = ActiveWorkbook


FileToOpen = Application.GetOpenFilename(Title:="Open the sourcefile", FileFilter:="Excel files (*.xlsx),")


If FileToOpen = False Then
    MsgBox "No file opened.", vbExclamation, "Message"
    Exit Sub
Else
    Set Source = Workbooks.Open(FileToOpen)
    
    Source.Worksheets("Sheet1").Range("A1:C10").Copy
        Target.Worksheets("Sheet1").Range("A1").PasteSpecial xlPasteValues
    
    Source.Close SaveChanges:=False
    
End If
    
End Sub

 

[TPFKAS]

What if you try

Sub Import()


Dim Source As Workbook, Target As Workbook



Set Target = ActiveWorkbook


FileToOpen = Application.GetOpenFilename(Title:="Open the sourcefile", FileFilter:="Excel files (*.xlsx),")


If FileToOpen = False Then
    MsgBox "No file opened.", vbExclamation, "Message"
    Exit Sub
Else
    Set Source = Workbooks.Open(FileToOpen)
    
    Source.Worksheets("Sheet1").Range("A1:C10").Copy
        Target.Worksheets("Sheet1").Range("A1").PasteSpecial xlPasteValues
    
    Source.Close SaveChanges:=False
    
End If
    
End Sub

Thanks again for your help.
Indeed, that works. Apparently the behaviour is different when using a second instance of Excel, but I am puzzled why...

 

[Fluff]

I've no idea why, but I've always found it best to do everything in the same instance of xl, it seems to be less prone to problems.