Copy data and paste in new sheet in new workbook

[KIM Chanthan]

Hi,

I want to copy data from one sheet and want to paste it in new sheet of another workbook (Master file).
Below code work perfectly as expected, but because of the link, all the data become #VALUE! when i reopen it or share master file to other people.
So could anyone tell me how to solve with it?

>>> use code - tags <<<

Sub ConsolidateBPbyccy()
Dim FolderPath As String
Dim Filename As String
Dim Sheet As Worksheet
Application.ScreenUpdating = False
FolderPath = ActiveWorkbook.Path & "\"
Filename = Dir(FolderPath & "*.xlsx*")
D = 1
Do While Filename <> ""
    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True

Worksheets("Product").Copy After:=ThisWorkbook.Sheets(D)
Workbooks(Filename).Close Savechanges:=False
ActiveSheet.Name = Range("C4").Value
Filename = Dir()
  D = D + 1
Loop
Application.AskToUpdateLinks = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Thanks,

 

[jolivanes]

If you want to refer to the 2nd sheet from the left, regardless of it's name, use

Sheets(2)

Conflicting explanation
Re: "want to copy data from one sheet and want to paste it in new sheet of another workbook"
Above means copying data from a worksheet and paste it in another worksheet.
But in the code you copy sheets.
Anyway, the error more then likely occurs because you have formulas in that sheet.
You can copy the sheet to another workbook and use

ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

Or copy the data and paste as values.
I don't know if you did but be carefull with ThisWorkbook and ActiveWorkbook
ThisWorkbook is the workbook that has the macro in it while the ActiveWorkbook can be any workbook but it would be the one that has the sheet that is showing on your desktop in it.
Do you want to copy one sheet ("Product" = 2nd Sheet from the left) from every workbook in a folder into your Master Workbook?

 

[jolivanes]

How far does this get you?
The Workbook with the code in it ("Master") needs th be saved in the same folder where the other workbooks are.

Sub Combine_One_Sheet_Into_Master()
Dim mydir As String, myfile As String, mybook As Workbook
Dim j As Long
    j = 1
    mydir = ThisWorkbook.path & "\"
    myfile = Dir(mydir & "*.xl*")
    Application.ScreenUpdating = False
    Do While myfile <> ""
        If myfile <> ThisWorkbook.Name Then
        Set mybook = Workbooks.Open(mydir & myfile)
            mybook.Sheets(2).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
                With ThisWorkbook.Sheets(Sheets.Count)
                    .Name = "Product " & j
                    .UsedRange.Value = .UsedRange.Value
                End With
            j = j + 1
        mybook.Close False
        End If
        myfile = Dir()
    Loop
    Application.ScreenUpdating = True
End Sub