I have got a code that works great for importing data from another closed workbook. I have no clue how to modify code so it will paste imported data as values.
Can u please help me to find solution ?
Option Explicit
Public Sub Import_SM_Export_DataFromAnotherWorkbook()
' Get workbook...
Dim targetWorkbook As Workbook
Set targetWorkbook = Application.ThisWorkbook
' get the customer workbook
Dim Filter As String
Filter = "Text files (*.csv),*.csv"
Dim Caption As String
Caption = "Please Select an input Security Matrix file "
Dim Ret As Variant
Ret = Application.GetOpenFilename(Filter, , Caption)
If VarType(Ret) = vbBoolean And Ret = False Then Exit Sub
Dim wb As Workbook
Set wb = Workbooks.Open(Ret)
'copy into a specific worksheet in your target workbook
wb.Worksheets(CopyFromHere).UsedRange.Copy targetWorkbook.Worksheets("PasteHere").Range("A1")
'close opened workbook without saving
wb.Close SaveChanges:=False
End Sub
Use .PasteSpecial
to paste as Values:
wb.Worksheets(CopyFromHere).UsedRange.Copy
targetWorkbook.Worksheets("PasteHere").Range("A1").PasteSpecial Paste:=xlPasteValues
Also, you should set Application.CutCopyMode = False
afterwards.
But ideally you'd transfer the data without copy paste entirely. somewhat like this:
dim sourceRng as String
sourceRng = wb.Worksheets(CopyFromHere).UsedRange.Address
targetWorkbook.Worksheets("PasteHere").Range(sourceRng).Value = wb.Worksheets(CopyFromHere).Range(sourceRng).Value