Warm tip: This article is reproduced from serverfault.com, please click

VBA to import data from closed workbook and paste into specific worksheet as values

发布于 2020-12-01 13:19:51

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
Questioner
Radosław Sokolowski
Viewed
11
horst 2020-12-01 21:35:01

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