Create sheets for additions to list on sheet one. [Solved]

Michelle - Jun 18, 2018 at 07:52 AM - Latest reply: TrowaD 2391 Posts Sunday September 12, 2010Registration dateModeratorStatus July 12, 2018 Last seen
- Jul 2, 2018 at 11:21 AM
I have a running list of names in a workbook that are kept in alphabetical order on sheet one. Those names are updated daily with new additions. Each name on the list has a corresponding Sheet with matching account number/address/etc. As the files open and close the names are added and removed from the list. I currently have a blank copy of my form with all my ranges and codes as the second Sheet. I move/copy this Sheet for each new name on the list every day.

Is there a code that will allow me to add a new Sheet automatically with the Name and Account Number in the correct place when a new name is added to list?

Thank you for any help!
See more 

Your reply

9 replies

TrowaD 2391 Posts Sunday September 12, 2010Registration dateModeratorStatus July 12, 2018 Last seen - Jun 18, 2018 at 12:13 PM
0
Thank you
Hi Michelle,

Sure, could you let us know in which column you enter the Sheet names, Name and Account Number? Also when the new sheet is created, where does the Name and Account Number go?

The idea is that whenever you enter the Account Number, a new sheet is automatically created. Will that work for you, or do you have something else in mind?

Best regards,
Trowa

MLingbeck 4 Posts Monday June 18, 2018Registration date June 29, 2018 Last seen - Jun 18, 2018 at 02:13 PM
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
Respond to TrowaD
TrowaD 2391 Posts Sunday September 12, 2010Registration dateModeratorStatus July 12, 2018 Last seen - Jun 19, 2018 at 12:14 PM
0
Thank you
Hi Michelle,

See if the following codes do the job for you:
Code for the 'A-Z' sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim DestSheet As Worksheet
If Intersect(Target, Columns("C")) Is Nothing Then Exit Sub

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

Set DestSheet = ActiveSheet

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

Code for the 'A Blank' sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SourceSheet As Worksheet
If Intersect(Target, Range("J2")) Is Nothing Then Exit Sub

Set SourceSheet = ActiveSheet

Workbooks.Open Filename:="YourPath\Complete2018.xlsx"
SourceSheet.Move After:=Workbooks("Complete2018.xlsx").Sheets(Sheets.Count)
End Sub


Change 'YourPath' to your path and check the extention of your 'Complete2018' file.

Let me know your experience.

Best regards,
Trowa
TrowaD 2391 Posts Sunday September 12, 2010Registration dateModeratorStatus July 12, 2018 Last seen - Jun 21, 2018 at 11:33 AM
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
MLingbeck 4 Posts Monday June 18, 2018Registration date June 29, 2018 Last seen - Jun 21, 2018 at 02:20 PM
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
TrowaD 2391 Posts Sunday September 12, 2010Registration dateModeratorStatus July 12, 2018 Last seen - Jun 25, 2018 at 11:26 AM
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
MLingbeck 4 Posts Monday June 18, 2018Registration date June 29, 2018 Last seen - Jun 29, 2018 at 07:30 AM
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
TrowaD 2391 Posts Sunday September 12, 2010Registration dateModeratorStatus July 12, 2018 Last seen - Jul 2, 2018 at 11:21 AM
Thanks Michelle for your feedback, I really appreciate it. It was my pleasure to help you out!
Respond to TrowaD