Create sheets for additions to list on sheet one.

Hello Trowa,

The idea is that when ever I enter a start date for a new client line (account number, name, start date, lead) there will be a copy of my blank form created from that data and entered as a new Sheet. I currently have a running list on Sheet two titled A-Z. Sheet one is my blank form and is titled A Blank.

A-Z has the following columns: A: Name, B: Account Number, C: Start Date, D: Lead, E: Ref. Lead.
A Blank is my blank working form for each person.

A1-D1 is a merged cell where I would like the account number to automatically merge from column B of Sheet A-Z.
E1-K1 is a merged cell where I would like the name from column A of Sheet A-Z.
B2-C2 is a merged cell where I would like the start date from column C of Sheet A-Z.
E2-G2 is a merged cell where I would like the start date from column D of Sheet A-Z.
B3-D3 is a merged cell where I would like the Ref. Lead from column E of Sheet A-Z.

The new name for each sheet should be the same as the name in column A on Sheet A-Z.

The reason I specified "when I enter a start date" to copy the sheet, is this list is kept for running jobs. I currently manually move the sheet to another excel file when the job is completed. I would like to be able to have the sheet move automatically when the completion date is entered- if that is possible.

If it is needed, the working file names are InProgress2018.xls and the other Complete2018.

Please let me know if additional information is needed.

Best regards,
Michelle

Good morning Trowa,

Thank you for the assistance. The first part of my Workbook code is working well and I am so excited! I entered the code you sent as suggested. As I enter data for a client this is what happens...

I enter cell A: Name, B: Account Number, NEW PAGE OPENS HERE, then I go need to go back to the A-Z Sheet to keep entering the data -C: Start Date, D: Lead, E: Ref. Lead. Unfortunately, the data does not flow to the newly created page at this point. I have worked with it for the last couple of days. If I skip column B and enter it LAST, entering C,D,E first. Then go back and enter B, the data all flows through correctly to the new Sheet. This is not an ideal workflow, but it is progress.

On the individual new Sheets once they are created, they are self naming perfectly! It took me a little bit to get the Path accurate for them because I am working with a shared drive setup. Once I determined the proper path, the discharge date seems to works well in all my tests. The Sheets transfer between Workbooks as expected.

I need to correct the issue with the data flow on Sheet A-Z with cells C, D, E. At this point, which would be easier- to have the data flow into the newly created sheets after they were created OR to change where the new sheet creates- have it create the Sheet after E? Or something else like an additional column with an indicator to create new Sheet? I am leaning towards the addition of an additional column with an "X" indicator when ready to create a new Sheet, but would like to know what you think. In your opinion, what would be the best?

Warm regards,
Michelle

Hi Michelle,

Thanks for your clear feedback.

You wrote:
' when ever I enter a start date for a new client line (account number, name, start date, lead) there will be a copy of my blank form created '

Since you enter the Start date in column C the new sheet should be created after entering a value in column C. This is what this code line does:

If Intersect(Target, Columns("C")) Is Nothing Then Exit Sub

Let me explain a bit so you can change that column yourself. Either to column E or to a column of your choosing where you could put an 'X' in, that is up to you.

The code line mentioned above refers to the column, that when a change is made, the code will run. Once you have changed that column letter, all you have to do now is change the offset values.
So let's take a look at this part, the first offset value: Target.Offset(0, -2).Value
'Target' stands for the cell where the change is made, for example C5. The offset value changes that cell reference. First the rows and then the columns. Positive values are to the right/down and negative to the left/up. So Offset(0,-2) means that from C5 we move 0 cells down and 2 cells to the left. We end up at A5.
When you change:

If Intersect(Target, Columns("C")) Is Nothing Then Exit Sub

into:

If Intersect(Target, Columns("E")) Is Nothing Then Exit Sub

You need to change the offset value from (0,-2) to (0,-4) to still end up at A5 to get the sheet name.

Adjust the other offset values accordingly.

Do let me know how that works out for you.

Kind regards,
Trowa

Hello Trowa,

Thank you for explaining how the code works. I am very glad to know how to change the code myself so that I can make changes for future use.

I was able to make the changes and the data is falling in correctly now.

As I was testing the new values I discovered an issue. Anytime I add a Row, delete a Row or delete a name from my list on Sheet A-Z I get an Error : Run-time error '1004' Application-defined or object-defined error. This happens when deleting blank lines or names. It does not happen when deleting Sheets in the Workbook- just lines on the one Sheet A-Z.

Did I cause an issue some where with my changes? This is what I have:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DestSheet As Worksheet
If Intersect(Target, Columns("E")) Is Nothing Then Exit Sub

Sheets("1 Blank").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Target.Offset(0, -4).Value

Set DestSheet = ActiveSheet

DestSheet.Range("A1").Value = Target.Offset(0, -3).Value
DestSheet.Range("E1").Value = Target.Offset(0, -4).Value
DestSheet.Range("C2").Value = Target.Value
DestSheet.Range("B2").Value = Target.Offset(0, -2).Value
DestSheet.Range("E2").Value = Target.Offset(0, -1).Value
DestSheet.Range("B3").Value = Target.Offset(0, 0).Value
End Sub

Thank you for your help.

Michelle

Hi Michelle,

When you add or remove a row, then more then 1 cell in column E changes and Excel gets confused. To only let the code run when 1 cell is changed, then place the following code line below the 'If Intersect' (3rd code line) one:

If Target.Cells.Count <> 1 Then Exit Sub

Kind regards,
Trowa

Good morning Trowa,

I apologize for the slow response. I was away from the office and did not have a chance to add the new line of code and test it until this morning. This new line has fixed the final issue.

Thank you for your assistance in both setting up the code and explaining to me how to understand it better. I believe I will be able to change the code as I need now that I do understand it a little better.

This new workbook will speed up my workflow and save me time every day! Thank you!

Most sincerely,
Michelle