Make macro create unique files only once.If files exist amend them.

[DocAElstein]

Hi RaghavendraPrabhu,
Welcome to ExcelFox.
You explained your question very well

If unique file does not exists then
Create unique file

If unique file does not exist....,,.... --- To test this we can use the VBA Dir Function
This function returns the full File Path found for a (file search criteria) .
So if you pass it the full file path of the File you are looking for as search criteria, then it simply returns the same file back if it finds it. If it does not find it then it returns “”
This modification will only add your file If it does not exist.
Else it opens your file

    For x = 0 To ws.Cells(ws.Rows.Count, uCol).End(xlUp).Row - 1
    If unique(x) <> "" Then
    If Dir(ThisWorkbook.Path & "\" & unique(x) & ".xlsx", vbNormal) = "" Then 'If unique file does not exist
        'add workbook
        Workbooks.Add: Set wb(x) = ActiveWorkbook
        ws.Range(ws.Cells(1, 1), ws.Cells(1, uCol)).Copy wb(x).Sheets(1).Cells(1, 1) '_-**
    Else ' open workbook
     Workbooks.Open Filename:=ThisWorkbook.Path & "\" & unique(x) & ".xlsx"
     Set wb(x) = ActiveWorkbook
    End If

'_-** In addition I have changed the position of the line that adds the header, so that it only adds the header to a newly Added file
_._____

.. I need help in modifying the code snippet shown below to prevent overwriting....]

The code as written appears to append data. So data added to an existing file is added ( appended ) as it appears that you wish to have.

_._______________

I also want to update column 6 of the master file and the unique files with the date the macro was run.

This code line will change the line just added to include the current date:

                wb(x).Sheets(1).Cells(WorksheetFunction.CountA(wb(x).Sheets(1).Columns(uCol)), 6).Value = Format(Date, "dd mmm yyyy")

Results, Using your supplied data:
Before running code: ( “Raju.xlsx” )
Using Excel 2007 32 bit

Row\Col	A	B	C	D	E	F	G
1	S No	Item	Price	Qty	Total	Date Distributed	Team Member
2	1	a1	12	20	240	17. Mrz 18	Raju
3	5	a13	15	15	225	17. Mrz 18	Raju
4	9	a5	12	20	240	17. Mrz 18	Raju
5	13	a9	12	20	240	17. Mrz 18	Raju
6	18	b4	5	15	75	17. Mrz 18	Raju
7	22	b8	5	15	75	17. Mrz 18	Raju
8	25	c10	10	5	50	17. Mrz 18	Raju
9	29	c14	10	5	50	17. Mrz 18	Raju
10	33	c18	10	5	50	17. Mrz 18	Raju
11	35	c2	10	5	50	17. Mrz 18	Raju
12	40	c6	10	5	50	17. Mrz 18	Raju
13	44	a15	13	20	260	18. Mrz 18	Raju

Worksheet: Sheet1
After running code:

Row\Col	A	B	C	D	E	F	G
1	S No	Item	Price	Qty	Total	Date Distributed	Team Member
2	1	a1	12	20	240	17. Mrz 18	Raju
3	5	a13	15	15	225	17. Mrz 18	Raju
4	9	a5	12	20	240	17. Mrz 18	Raju
5	13	a9	12	20	240	17. Mrz 18	Raju
6	18	b4	5	15	75	17. Mrz 18	Raju
7	22	b8	5	15	75	17. Mrz 18	Raju
8	25	c10	10	5	50	17. Mrz 18	Raju
9	29	c14	10	5	50	17. Mrz 18	Raju
10	33	c18	10	5	50	17. Mrz 18	Raju
11	35	c2	10	5	50	17. Mrz 18	Raju
12	40	c6	10	5	50	17. Mrz 18	Raju
13	44	a15	13	20	260	18. Mrz 18	Raju
14	1	a1	12	20	240	18. Mrz 18	Raju
15	5	a13	15	15	225	18. Mrz 18	Raju
16	9	a5	12	20	240	18. Mrz 18	Raju
17	13	a9	12	20	240	18. Mrz 18	Raju
18	18	b4	5	15	75	18. Mrz 18	Raju
19	22	b8	5	15	75	18. Mrz 18	Raju
20	25	c10	10	5	50	18. Mrz 18	Raju
21	29	c14	10	5	50	18. Mrz 18	Raju
22	33	c18	10	5	50	18. Mrz 18	Raju
23	35	c2	10	5	50	18. Mrz 18	Raju
24	40	c6	10	5	50	18. Mrz 18	Raju
25	44	a15	13	20	260	18. Mrz 18	Raju

At the end of the code, this will change the date in column 6 in the master File.

' Master File change to current date:
Dim Lr As Long: Let Lr = ws.Cells(Rows.Count, 6).End(xlUp).Row
 ws.Range("F2:F" & Lr & "").Value = Format(Date, "dd mmm yyyy")

_._____________

Here is the current full modified code :
http://www.excelfox.com/forum/showth...0537#post10537

Alan








Ref:
https://www.thespreadsheetguru.com/t...a-given-folder