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

**DocAElstein** · 03-20-2018, 04:12 PM

Hi Raghavendra
There is some inconsistency with post #3 and post #5 -
_ In post #3 the supervisor inputs columns A, B, C, D, F and G (via form).
Column E is blank indicating new work

_ In post #5 team leader inputs the columns B, C, D and G
column F is blank

I will go with the information from post #5…

This situation I take as the befores:
I will assume then that for the 20th March, the start point of the Master file looked something like this:

_Row\^Col	A	B	C	D	E	F	G
28	27	D3	$ 332.00	9	$ 2,988.00	19. Mrz 18	Raju
29	28	D4	$ 22.00	41	$ 902.00	19. Mrz 18	Ramesh
30		D5	$ 44.00	52	$ 2,288.00		Raghu
31		D6	$ 55.00	63	$ 3,465.00		Ravi
32		D7	$ 566.00	12	$ 6,792.00		Raju
33		D8	$ 332.00	32	$ 10,624.00		Ramesh
34		A9	$ 12.00	65	$ 780.00		Sangeeta
35		B9	$ 45.00	47	$ 2,115.00		Sangeeta
36		C9	$ 56.00	85	$ 4,760.00		Sangeeta
37		D9	$ 89.00	96	$ 8,544.00		Sangeeta
38

Worksheet: Sheet1

I will assume the File for Sangeeta does not exist yet.

For the other Files , Raghu , Ravi , Raju , Ramesh , I assume the start is with the entry for 20 March removed: For example for , Raghu

_Row\^Col	A	B	C	D	E	F	G
7	21	C5	655	65	42575	18. Mrz 18	Raghu
8	25	D1	1258	7	8806	19. Mrz 18	Raghu
9

Worksheet: Sheet1

The modifications are fairly simple
_ We no longer loop from the second row, 2 in the Master worksheet. Instead we Loop from the next free ( blank ) cell in column F ( 6 )
___For y = ws.Cells(ws.Rows.Count, 6).End(xlUp).Row + 1 To ………….

_ I included some new code lines earlier in the code just to make that above code line a bit tidier, and also to give the convenience of adding the dates to the new rows in one simple code line earlier on in the code and adding the S.no. to the new rows in one simple code line

Code:

'Your main worksheet info.
 Set ws = ActiveWorkbook.Sheets("Sheet1")
 Let uCol = 7 'Column G
Dim Strt As Long, Stp As Long: Let Strt = ws.Cells(ws.Rows.Count, 6).End(xlUp).Row + 1: Stp = ws.Cells(ws.Rows.Count, uCol).End(xlUp).Row
 Let ws.Range("F" & Strt & ":F" & Stp & "").Value = Format(Date, "dd mmm yyyy") ' adding the dates to the new rows
 Let ws.Range("A" & Strt & ":A" & Stp & "").Value = Application.Evaluate("=row(" & Strt & ":" & Stp & ")-1") ' adding the S.no. to the new rows

Those are the main changes. In addition
_ you no longer need to change or add any dates on the master as this has been done initially before looping through the peoples worksheets.
_ The Paste type needed to be changed to Paste Values and Number Formats so as to get the correct copied date and Price format

_.... ___________________________________

Results ( After )
The code with those modifications results in a final master worksheet of this:

_Row\^Col	A	B	C	D	E	F	G
29	28	D4	$ 22.00	41	$ 902.00	19. Mrz 18	Ramesh
30	29	D5	$ 44.00	52	$ 2,288.00	20. Mrz 18	Raghu
31	30	D6	$ 55.00	63	$ 3,465.00	20. Mrz 18	Ravi
32	31	D7	$ 566.00	12	$ 6,792.00	20. Mrz 18	Raju
33	32	D8	$ 332.00	32	$ 10,624.00	20. Mrz 18	Ramesh
34	33	A9	$ 12.00	65	$ 780.00	20. Mrz 18	Sangeeta
35	34	B9	$ 45.00	47	$ 2,115.00	20. Mrz 18	Sangeeta
36	35	C9	$ 56.00	85	$ 4,760.00	20. Mrz 18	Sangeeta
37	36	D9	$ 89.00	96	$ 8,544.00	20. Mrz 18	Sangeeta
38

Worksheet: Sheet1

Here are some example workbook results:
„Raghu.xlsx“

_Row\^Col	A	B	C	D	E	F	G
8	25	D1	1258	7	8806	19. Mrz 18	Raghu
9	29	D5	$ 44.00	52	$ 2,288.00	20. Mrz 18	Raghu
10

Worksheet: Sheet1

And here the newly created “Sangeeta.xlsx”

_Row\^Col	A	B	C	D	E	F	G
1	S No	Item	Price	Qty	Total	Date Distributed	Team Member
2	33	A9	$ 12.00	65	$ 780.00	20. Mrz 18	Sangeeta
3	34	B9	$ 45.00	47	$ 2,115.00	20. Mrz 18	Sangeeta
4	35	C9	$ 56.00	85	$ 4,760.00	20. Mrz 18	Sangeeta
5	36	D9	$ 89.00	96	$ 8,544.00	20. Mrz 18	Sangeeta
6