VBA Copy Paste Values

[paulcherianc]

Hi!

I am trying run the below macro. This should import a specific sheet from another workbook which contains values and formula. Then it creates a worksheet in the destination workbook and pastes all the cells with values.

But it is not converting formulas to values while pasting.

something wrong with the coding?

Sub ImportData()


Application.DisplayAlerts = False

    Dim ws As Worksheet
    Dim filter As String
    Dim targetWorkbook As Workbook, wb As Workbook
    Dim Ret As Variant
    Dim Caption As String
    Set targetWorkbook = Application.ActiveWorkbook


    filter = "Text files (*.xlsm),*.xlsm"
    Caption = "Please Select an input file "
    Ret = Application.GetOpenFilename(filter, , Caption)

    If Ret = False Then Exit Sub


    Set wb = Workbooks.Open(Ret)
   
     
   
    wb.Sheets("Main Data").Copy After:=targetWorkbook.Sheets(targetWorkbook.Sheets.Count)
    ActiveSheet.Name = "Main Data"
    Worksheets("Main Data").Select
    ActiveSheet.Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

   
    wb.Activate
    ActiveWorkbook.Close False
   
       MsgBox "Import Completed!", vbInformation, "Done"
       Application.ScreenUpdating = True
     
     
End Sub

 

[Hui]

You need to add a line to change worksheets

Selection.Copy
Worksheets("your destination worksheet").select
Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'Change address to suit

 

[paulcherianc]

Ok it worked! Thanks!