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

Hi Al,

I am a novice at 57 years of age with coding. At my work (still working) we were told we could not use MS Access anymore, so I have to learn MS Excel. With MS Access, I did not have to do much coding. With MS Excel coding I am at sea!

Basically, we have a few people in our team. We gave a file called xMaster.xlsm where the team leader enters our duties and clicks a button and the work is split. The files are formed at the same location. I found the code at the following place and modified it slightly so that the date stamp is not attached to the unique file created.

The site is https://stackoverflow.com/questions/...-a-column?rq=1
The code is as below

[code]
Sub ExportByName()
Dim unique(1000) As String
Dim wb(1000) As Workbook
Dim ws As Worksheet
Dim x As Long
Dim y As Long
Dim ct As Long
Dim uCol As Long
On Error GoTo ErrHandler
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'Your main worksheet
Set ws = ActiveWorkbook.Sheets("Sheet1")
uCol = 7 'Column G
ct = 0
For x = 2 To ws.Cells(ws.Rows.Count, uCol).End(xlUp).Row 'get a unique list of users
If CountIfArray(ActiveSheet.Cells(x, uCol), unique()) = 0 Then
unique(ct) = ActiveSheet.Cells(x, uCol).Text
ct = ct + 1
End If
Next x
For x = 0 To ws.Cells(ws.Rows.Count, uCol).End(xlUp).Row

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

Quote:

---

Originally Posted by RaghavendraPrabhu

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

Code:

---

  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
_._____

Quote:

---

Originally Posted by RaghavendraPrabhu

.. 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.

_._______________

Quote:

---

Originally Posted by RaghavendraPrabhu

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:

Code:

---

                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.

Code:

---

' 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

Hi DocAElstein

Thanks for the amending the code. Needs some minor changes though.

My apologies I did not mention that my supervisor inputs only columns A, B, C, D, F and G (via form).

Column E is blank indicating new work. So when he runs the macro the second time or more, only these new records with blank column E have to be appended if the unique workbook is available. Then update with the date the macro is run. Otherwise the amending of the code is perfect.

Hi RaghavendraPrabhu,
I am sorry but I do not quite understand what you are saying in your last post.

If you want more help then can you try to explain again.

Perhaps you can show me with sample data what you want done.

Show or explain to me the typical Before situation, and then show or explain what the After should then look like when the code has run

Remember that any explanation will appear OK to you because you know the project well. You need to try to explain so that someone like me who has no idea about it can understand.

Possibly the explanation lies in the Files you have already supplied. I was / am not too clear on exactly what all those file are.
I assumed the master file had already been filled from the data files, and as I had no new data files I simply used them again so as to demonstrate how the modified code would append data

On the face of it I think it sounds fairly easy to do what you want, but it is not clear to me presently exactly what it is that you want
Alan

Hi DocAElstein,

Sorry about replying so late. I cannot access the website from my work.

The team leader inputs the data in columns B, C, D and G (via a form)

He inputs S.no. 1 to 8 on 16 and ran the macro on 16MAR18.

He inputs S.no. 9 to 16 and ran the macro on 17MAR18

He inputs S.no. 17 to 24 and ran the macro on 18MAR18

He inputs S.no. 25 to 28 and ran the macro on 19MAR18

He inputs S.no. 29 to 36 and also a new member Sangeeta and ran the macro on 20MAR18

In other words, when the new data is input, column F is blank and the cells get updated on the day the macro is run.

The individual files have dates in column F of the day the macro is run for different records.

Hope this gives a better picture.


Regards
Raghavendra


Attachment 1991

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	H
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	H
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

Worksheet: Tabelle1

Here the latest code:
http://www.excelfox.com/forum/showth...0542#post10542


Alan

Hi DocAElstein,

Thank you very much! We have 16 in the team and over 800 lines are input every day. My supervisor was sorting and copying and pasting and it was a big mess.

Thank you very much for your help and input. I now consider this as solved.

Regards

Raghavendra Prabhu

Hi Raghavendra
Yous are welcome, thanks for he feedback
Alan

P.S. If Yous would like explanations to any of the codes that I supply, then ask and I will add an explanation post to the Threads at some later date.