Copy paste values only

TPFKAS

Board Regular
Joined
Mar 1, 2010
Messages
54
Hi there,

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

Code:
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?
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,943
Office Version
365
Platform
Windows
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

Board Regular
Joined
Mar 1, 2010
Messages
54
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:


Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,943
Office Version
365
Platform
Windows
What if you try
Code:
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

Board Regular
Joined
Mar 1, 2010
Messages
54
What if you try
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,943
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,140
Messages
5,412,691
Members
403,441
Latest member
speedtobeat

This Week's Hot Topics

Top