Excel VBA Copy and Paste to New file

July 27, 2022

I’m trying to copy data from one sheet in WorkbookA and paste VALUES in a new workbook. Below is my code. The issue is with Step 3, I can’t seem to get it to paste values. Anyone know how to fix this?

Sub SaveFile()
'Step 1 Copy the data
'You will probably need to change the sheet name, and the range address
Sheets("Sheet1").Range("A1:E12").Copy

'Step 2 Create a new workbook
Workbooks.Add

'Step 3 Paste the data
'You will probably need to change the target range address

**ActiveSheet.Paste Destination:=Range("A1").PasteSpecial = xlPasteValues**

'Step 4 Turn off application alerts
Application.DisplayAlerts = False

'Step 5 Save the newly created workbook
'You will probably need to change the the save location.
ActiveWorkbook.SaveAs Filename:="D:\Temp\MyNewWorkBook.CSV"

'Step 6 Turn application alerts back on
Application.DisplayAlerts = True
End Sub

>Solution :

A better approach would not be to copy-paste values at all but rather set the values.

Sub SaveFile()
'Step 1 Copy the data
'You will probably need to change the sheet name, and the range address
'use this variable...
Dim copyRange As Range
Set copyRange = Sheets("Sheet1").Range("A1:E12")

'Step 2 Create a new workbook
Workbooks.Add

'Step 3 Paste the data
'You will probably need to change the target range address
Range(copyRange.Address).Value = copyRange.Value

'rest of your code...

Or if you wanted to truly use copy paste (Sometimes this is good for date values)… this should work…

Sub SaveFile()
'Step 1 Copy the data
'You will probably need to change the sheet name, and the range address
Sheets("Sheet1").Range("A1:E12").Copy

'Step 2 Create a new workbook
Workbooks.Add

'Step 3 Paste the data
'You will probably need to change the target range address

ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues

'Step 4 Turn off application alerts
Application.DisplayAlerts = False

'Step 5 Save the newly created workbook
'You will probably need to change the the save location.
ActiveWorkbook.SaveAs Filename:="D:\Temp\MyNewWorkBook.CSV"

'Step 6 Turn application alerts back on
Application.DisplayAlerts = True
End Sub