Copying Values between workbooks

[termeric]

Hello, I am using the code below to loop through a series of workbooks and copy 2 of the tabs over. I'm running into a problem where some of the formulas included on the sheet are If statements, and they end up all false because i am not copying over the tab that they are analyzing. is there a way to copy over the values in the cells instead of the formulas?

    xSheetName = "Rate Sch ED6"
    xRgStr = "A1:G50"
    zSheetName = "Rate Sheet"
    zRgStr = "A1:G50"
    
    Set xFileDlg = Application.FileDialog(msoFileDialogFolderPicker)
    With xFileDlg
        If .Show = -1 Then
            xSelItem = .SelectedItems.Item(1)
            Set xWorkBook = ThisWorkbook
            Set xSheet = xWorkBook.Sheets("New Sheet")
            If xSheet Is Nothing Then
                xWorkBook.Sheets.Add(after:=xWorkBook.Worksheets(xWorkBook.Worksheets.Count)).Name = "New Sheet"
                Set xSheet = xWorkBook.Sheets("New Sheet")
            End If
            
            Set zSheet = xWorkBook.Sheets("New Sheet 2")
            If zSheet Is Nothing Then
                xWorkBook.Sheets.Add(after:=xWorkBook.Worksheets(xWorkBook.Worksheets.Count)).Name = "New Sheet 2"
                Set zSheet = xWorkBook.Sheets("New Sheet 2")
            End If
            xFileName = Dir(xSelItem & "\*.xlsm", vbNormal)
            If xFileName = "" Then Exit Sub
            Do Until xFileName = ""
                Sheets("New Sheet").Cells.Delete Shift:=xlUp
                Sheets("New Sheet 2").Cells.Delete Shift:=xlUp
            
                Set xBook = Workbooks.Open(xSelItem & "\" & xFileName)
                Set xRg = xBook.Worksheets(xSheetName).Range(xRgStr)
                xRg.Copy xSheet.Range("A65536").End(xlUp).Offset(0, 0)
                
               
                Set xBook = Workbooks.Open(xSelItem & "\" & xFileName)
                Set zRg = xBook.Worksheets(zSheetName).Range(zRgStr)
                zRg.Copy zSheet.Range("A65536").End(xlUp).Offset(0, 0)
                
                xFileName = Dir()
                xBook.Close
                
                'massage the data
                GroupInfo
                RateSch
                Sheets("Group Info Summary").Range("A" & x + 13).Value = x
                x = x + 1

                
                
            Loop
        End If
    End With
[\code]

 

[GlennUK]

Instead of doing a straight copy to the destination, do a copy (into the clipboard), then do a PasteSpecial of values into the destination.

Like:
xRg.Copy xSheet.Range("A65536").End(xlUp).Offset(0, 0).PasteSpecial Paste:=xlPasteValues

 

[Fluff]

Another option, bypassing the clipboard.

xSheet.Range("A65536").End(xlUp).Offset(0, 0).Resize(xRg.Rows.Count, xRg.Columns.Count).Value = xRg.Value