How to modify the code so that the files are not overwritten?

Question

In my team at work, we have 6 people and our team leader allocates work. He inputs the details in a worksheet named zMaster.xlsm with the following headings.

Item Qty Price Total Invoice Team Mbr Date Alloc A1 22 $44.21 $972.62 AD14256 Raghu
A2 10 $210.44 $2104.40 AD14257 Ravi
A3 22 $10.00 $220.00 AD14258 Raghu

There could be hundreds of lines in the morning he clicks on a button and the following sheets are created within the same folder named Raghu.xlsx

Item Qty Price Total Invoice Team Mbr Date Alloc A1 22 $44.21 $972.62 AD14256 Raghu
A3 22 $10.00 $220.00 AD14258 Raghu

And this one is named Ravi.xlsx

Item Qty Price Total Invoice Team Mbr Date Alloc A2 10 $210.44 $2104.40 AD14257 Ravi

I have found the code to do this.

I need slight modification to make it work for me.

The code should also input the date in the “Date Alloc” field.

The code if run again overwrites the file name if it exists.

I don’t the files to be overwritten. I want the new work to be added to the next blank line in each team member’s file. The code I found is as from the web pages

How to create a new Workbook for each unique value in a column?

I have modified only one line of the code so as not to append the date stamp at the end of the name.

Thank you to all

Regards

Raghu

please format data properly. Maybe use a markdown table generator. Also, where is the code you have written? — QHarr, Mar 10 '18 at 21:33
Welcome to Stack Overflow! It's unclear what you're trying to do. Just [check if a file exists?](https://stackoverflow.com/q/16351249/8112776). This isn't a "free code writing service", it's more of a place that developers (of any experience level) can bring a *specific* question about a *specific* issue, **share their existing code** and explain what's been tried & researched so far, along with **examples** of related data and desired result. See [mcve] and [help/on-topic] as well as [these tips](https://codeblog.jonskeet.uk/writing-the-perfect-question/) and then please [edit] your question. — ashleedawg, Mar 11 '18 at 08:59

score 0 · Answer 1 · answered Mar 19 '18 at 16:21

Even though i'm a beginner in vba but Here is an idea on how you can test the existance of a workbook and avoid the overwriting, maybe you'll need to add necessary loops depending on your needs.

Sub WorkbookExistance()
Dim pathFilename As String
pathFilename = "C:\Users\XXXXX\Desktop\Raghu.xls"

'if the workbook exists
If Dir(pathFilename) <> "" Then
    'open the workbook
    Workbooks.Open fileName:=pathFilename
    'get the filename from the full path
    GetFileName = Right(pathFilename, Len(pathFilename) - InStrRev(pathFilename, "\"))
    'activate the workbook
    Workbooks(GetFileName).Activate
    'get the last non empty row in the sheet to avoid the overwritting
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row

'if the workbook doesn't exist
Else
    Dim wb As Workbook
    ' add the workbook
    Set wb = Workbooks.Add
    ' and save it
    wb.SaveAs fileName:=pathFilename
End If End Sub

How to modify the code so that the files are not overwritten?

1 Answers1