Hello,
I need your help with incorporating (Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False) to my vba below.
The goal is to copy & paste values as entered before exporting to a new csv file type. Thank you for any help provided.
VBA Code:
Private Sub CommandButton1_Click()
Dim WB_CSV As Workbook
Dim MyFileName As String, MyFolder As String
MyFileName = Trim(Sheets("PROF_Data_Capture").Range("B2").Value)
MyFolder = "C:\My Data\Policies\Employees"
If MyFileName <> "" Then
With CreateObject("Scripting.FileSystemObject")
If Not .FolderExists(MyFolder) Then
MsgBox "Folder not found:" & vbCr & MyFolder, vbOKOnly Or vbExclamation, "File Folder Error"
Exit Sub
End If
End With
MyFileName = Split(MyFileName, ".")(0) & ".csv"
With Sheets("PROF_Data_Capture")
.Unprotect
.Copy
Set WB_CSV = ActiveWorkbook
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
With WB_CSV.Worksheets(1)
.Range("A1:B36").Value = .Range("A1:B36").Value
End With
Application.DisplayAlerts = False
WB_CSV.SaveAs Filename:=MyFolder & "\" & MyFileName, FileFormat:=xlCSV
Application.DisplayAlerts = True
WB_CSV.Close False
MsgBox "CSV file " & MyFileName & " created in folder:" & vbCrLf & vbCrLf _
& MyFolder, vbOKOnly Or vbInformation, "File Export"
End If
End Sub
I need your help with incorporating (Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False) to my vba below.
The goal is to copy & paste values as entered before exporting to a new csv file type. Thank you for any help provided.
VBA Code:
Private Sub CommandButton1_Click()
Dim WB_CSV As Workbook
Dim MyFileName As String, MyFolder As String
MyFileName = Trim(Sheets("PROF_Data_Capture").Range("B2").Value)
MyFolder = "C:\My Data\Policies\Employees"
If MyFileName <> "" Then
With CreateObject("Scripting.FileSystemObject")
If Not .FolderExists(MyFolder) Then
MsgBox "Folder not found:" & vbCr & MyFolder, vbOKOnly Or vbExclamation, "File Folder Error"
Exit Sub
End If
End With
MyFileName = Split(MyFileName, ".")(0) & ".csv"
With Sheets("PROF_Data_Capture")
.Unprotect
.Copy
Set WB_CSV = ActiveWorkbook
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
With WB_CSV.Worksheets(1)
.Range("A1:B36").Value = .Range("A1:B36").Value
End With
Application.DisplayAlerts = False
WB_CSV.SaveAs Filename:=MyFolder & "\" & MyFileName, FileFormat:=xlCSV
Application.DisplayAlerts = True
WB_CSV.Close False
MsgBox "CSV file " & MyFileName & " created in folder:" & vbCrLf & vbCrLf _
& MyFolder, vbOKOnly Or vbInformation, "File Export"
End If
End Sub