Copy paste values only

TPFKAS

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

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
PasteValues, does what it says on the tin.
Can you provide you entire code, as from what you've posted & surprised it even runs.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top