Copying data from a specific folder to a master workbook and then plotting the data

Hi All,

I would have some questions about the following problem:

1. I want to copy data from .xlsx files from a specific folder to a master workbook, I've already made a code for it, but the problem is that I don't know how to make copies of the new files. For example if I have new files in the folder I would only copy the latest ones without duplicating existing ones.

My code is the following:

Sub MergeWorkbooks()
Path = "C:\Users\uix07585\Documents\Production\"
Filename = Dir(Path & "*.xlsx")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=False
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=Workbooks("Mergerecords.xlsm").Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub

Also every wb is merged to a new sheet as you can see.

2. I want plotting the data from other sheets on the first sheet which is the parent one. So basically what I need is that when I add a new .xlsx files to the folder (all of them has data in the same columns and rows) copy that workbook into a new sheet of the masterwb and also plot the incoming data on the first sheet.

Is that possible?

For now all I have is this code for making the charts:

Sub MakeChart()
Dim rX1 As Range, rY1 As Range
Dim rChartPos As Range
Dim chtO As ChartObject
Set rX1 = Worksheets("BMW DCDC Thickness Measure (29)").Range("A10:A2057"): Set rY1 = Worksheets("BMW DCDC Thickness Measure (29)").Range("B10:B2057")
Set rChartPos = Worksheets("Sheet1").Range("C2:J25")
With rChartPos
Set chtO = Sheet1.ChartObjects.Add(Left:=50, Top:=50, Width:=700, Height:=200)
chtO.Name = "BMW DCDC"
End With
With chtO.Chart
.ChartType = xlXYScatterLines
End With
.SetElement (msoElementChartTitleAboveChart)
.ChartTitle.Text = "Thickness 1"
End With
End Sub

Thanks in advance!

Hello Roy!

Thanks for the information about the Forum!

So what I need to do is that where I work, we have tensile tests. I will get three different data from one test, meaning that on chart I would have three lines. The test's data will be saved in a specific folder as .xlsx file containing only one sheet. The problem which I haven't solved yet is that every time I use the macro, it copies everything from that folder. Instead I need that only the newest .xlsx files would be copied to my wb while keeping the older ones in the wb at the same time.

Also I need that every time I copy a sheet into my wb with the data, a chart should be made on the first sheet (which is the main or parent one).

I will attach a file where you can see the main wb where all previous files were merged.

You should see charts on the 1st sheet "Munka1" where you can see the 3 lines of each chart containing 3 consecutive measurements.

Now at this point I have to change the sheet names manually to plot a new chart. It should be automated if it's possible, like I don't know every time 3 new sheet is being created, the code should plot those sheet's data into 1 chart shown on the 1st Sheet.

Mergerecords.xlsm