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!