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
CodePudding user response:
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