.Copy To Destingation as Values

[Steven101]

Hi I have the following code that copies data from various source "Data" sheets and pastes the data in a "Master" sheet. My only problem is the data pasted in in teh same format as the original Data sheet, which holds formulas. I need the Master sheet to only have the values. Any help would be appreciated. Thanks

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Dim LastRow As Long
    Const strPath As String = "C:\Desktop\Fulfilment Tracker SG\Individual TL Trackers\"
    ChDir strPath
    strExtension = Dir("*.xlsx*")
        Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            LastRow = .Sheets("Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets("Data").Range("A2:X" & LastRow).Copy wkbDest.Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Untested, but try this change

.Sheets("Data").Range("A2:X" & LastRow).Copy wkbDest.Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
wkbDest.Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Steven101]

Thanks Peter_SSs.

So weirdly I have now moved the file from my desktop to a network file path however the code below seems to still look at the desktop directory rather than the networkpath folder?

strExtension = Dir("*.xlsx*")

Any suggestions would be appreciated.

 

[Marc L]

Hi,​

​

when initializing the VBA function Dir do not forget to include the folder path …​

 

[Steven101]

Yip, sorted

Thanks Marc L

 

[Marc L]

​

Well done !​

 

[Peter_SSs]

Thanks Peter_SSs.

You're welcome. Glad it worked for you.
(Still would be good to know your version(s) )