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

Question

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

You do not import data from a closed workbook... Your code opens the workbook and is able to copy something only after this step. Being a csv file, it has only a single sheet. You can avoid Copy - Paste sequence and replace wb.Worksheets(CopyFromHere).UsedRange.Copy targetWorkbook.Worksheets("PasteHere").Range("A1") with targetWorkbook.Worksheets("PasteHere").Range("A1").Resize(wb.Worksheets(1).UsedRange.Rows.count, wb.Worksheets(1).UsedRange.Columns.count).value = wb.Worksheets(1).UsedRange.value — FaneDuru
– FaneDuru, Commented Dec 1, 2020 at 13:49
U are absolutely right. It opens closed workbook, copy data and close it:) Anyway this first solution provided works great. It opens, copy data and paste as values and then close source workbook. Great mate. Thank you for your help — Radosław Sokolowski
– Radosław Sokolowski, Commented Dec 2, 2020 at 10:13

horst · Accepted Answer · 2020-12-01 13:35:01Z

1

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

edited Dec 1, 2020 at 13:35

answered Dec 1, 2020 at 13:25

horst

7131 gold badge6 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related