我有一个代码非常适合从另一个封闭的工作簿中导入数据。我不知道如何修改代码,因此它将导入的数据粘贴为值。
你能帮我找到解决办法吗?
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
使用.PasteSpecial
粘贴为值:
wb.Worksheets(CopyFromHere).UsedRange.Copy
targetWorkbook.Worksheets("PasteHere").Range("A1").PasteSpecial Paste:=xlPasteValues
另外,你应该在Application.CutCopyMode = False
之后进行设置。
但是理想情况下,你无需完全复制粘贴就可以传输数据。有点像这样:
dim sourceRng as String
sourceRng = wb.Worksheets(CopyFromHere).UsedRange.Address
targetWorkbook.Worksheets("PasteHere").Range(sourceRng).Value = wb.Worksheets(CopyFromHere).Range(sourceRng).Value