copy whole data from closed excel sheet and paste as a value and formatting to active workbook

user_15587518 0
Viewed 113

ı have a problem about copy and pasting data as a value to the active workbook.

ı use this VBA code but this code paste data with formulas. but ı dont want formulas. ı want just values.

here is my code.

Sub CopySheetFromClosedWB()
Application.ScreenUpdating = False
    Set closedBook = Workbooks.Open("D:\A\B\C.xlsx")
    closedBook.Sheets("SYS").Copy Before:=ThisWorkbook.Sheets(1)
    closedBook.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub
vba excel
1 Answers

Copy Values

  • s - Source, d - Destination
Option Explicit

Sub CopySheetFromClosedWB()
    
    Application.ScreenUpdating = False
    
    Dim dwb As Workbook: Set dwb = ThisWorkbook
    
    Dim swb As Workbook: Set swb = Workbooks.Open("D:\A\B\C.xlsx")
    swb.Worksheets("SYS").Copy Before:=dwb.Sheets(1)
    swb.Close SaveChanges:=False
    
    With dwb.Worksheets(1).UsedRange
        .Value = .Value
    End With
    'dwb.save
    
    Application.ScreenUpdating = True

End Sub
user_98140690
Related