PDA

View Full Version : Excel Vba Code To Create New Worksheets When Range Is Updated



becky halsey
03-03-2016, 07:36 AM
I am working on an employee list. I have a code in place that created a worksheet for each employee and named the sheet based on employee name. What I need now is for it to create new sheets each time an employee is added, name the sheet based on employee name and paste a template in each sheet. Is this possible?

Thanks for your help!

skywriter
03-03-2016, 09:07 AM
It's easy enough, with the right details or preferably a sample workbook.

To attach a sample workbook click reply and then a new button labeled "Go Advanced" will appear.

Click the new button and then you will notice a paperclip icon on the toolbar, click that icon and follow the instructions to upload a workbook.

becky halsey
03-03-2016, 09:28 AM
Here is the attached file. The employee list is connected to another workbook. When the other workbook is updated the file "Sample file" is updated. I need it to create a new sheet from the template sheet for each new person added. The employee name I need it to use starts with column X2. If possible, I would also like for it to update upon opening each time. If it would be easier I can attach a file with just the employee information with no tabs already created. Just let me know.

Thanks a bunch for your time!

15544

skywriter
03-03-2016, 09:50 AM
I can write code that will look at the names in the x column and if there is no sheet in your workbook with that name it will copy the template and name the copy based on the name in column X.

If you are updating the sheet with the names in column X and you want the code to run each time that happens, then I'm not sure how to do that. I've never done that before. I am capable, but don't know what event that would trigger in the workbook. Usually when I write code based on the changing of a particular sheet it is based on one cell being changed. If you are doing some kind of mass update I'm not sure what to do.

I could give you a button that you could click that would run code that would run through every name in column X and create sheets for those that don't exist.

I have no idea how much having to check all the names will slow the code down, maybe not much at all, as opposed to being able to distinguish which cells in column X are those that previously existed and should be skipped.

I could have code that runs when the workbook opens or the sheet is activated if it wasn't already, but I don't know how or when this updating you speak of takes place.

I'll write code that goes through all of column X based on your limited data and you can test it out and give your opinion on what I wrote above.

becky halsey
03-03-2016, 10:31 AM
That sounds great! Can it be written where I can just change the column it is pulling from in the code in case I need to make changes to the spreadsheet? Also, the button would work. We can just click on it when we open it.

Thanks again!





I can write code that will look at the names in the x column and if there is no sheet in your workbook with that name it will copy the template and name the copy based on the name in column X.

If you are updating the sheet with the names in column X and you want the code to run each time that happens, then I'm not sure how to do that. I've never done that before. I am capable, but don't know what event that would trigger in the workbook. Usually when I write code based on the changing of a particular sheet it is based on one cell being changed. If you are doing some kind of mass update I'm not sure what to do.

I could give you a button that you could click that would run code that would run through every name in column X and create sheets for those that don't exist.

I have no idea how much having to check all the names will slow the code down, maybe not much at all, as opposed to being able to distinguish which cells in column X are those that previously existed and should be skipped.

I could have code that runs when the workbook opens or the sheet is activated if it wasn't already, but I don't know how or when this updating you speak of takes place.

I'll write code that goes through all of column X based on your limited data and you can test it out and give your opinion on what I wrote above.

skywriter
03-03-2016, 10:39 AM
You have a lot of blank row at the bottom of the table. Column X appears to be empty in those row, but they aren't, the cells appear to have spaces in them. This will cause issues, so those blank rows need to be deleted as I have done in the sample workbook.

There is a 31 character limit for a sheet name. Your values in the x column start with a number and then there are some spaces before the name. I wrote code that will cut off the last characters if needed to fit into the limit, so if you have any sheets with the name cut off it is because of a long name that combined with the spaces after the number have gone over the limit. I don't think there are any in existence because originally I wrote the code without the limit and didn't get any errors.

The first time the code is run it's a little slow because there are no sheets and it has to create all of them. My concern with running the code each time and having it go through all the names checking to see if a sheet exists seems to be a non issue as it does this very fast.

Try out the macro, I have it set to run from control + q.

Here's the code and below that is your sample workbook with the blank rows removed and all the employee sheets deleted so you can see the code adding them all.


Sub NewSheets()
Dim r As Range
Application.ScreenUpdating = False
With Worksheets("Sheet1")
For Each r In .ListObjects("Table_CURRENT_EMPLOYEE_S__SSN").ListColumns("F27").DataBodyRange
If Not r.Value = Empty Then
If Not Evaluate("ISREF('" & r.Value & "'!A1)") Then
Worksheets("template").Copy After:=Worksheets(Sheets.Count)
If Len(r.Value) > 31 Then r.Value = Left(r.Value, 31)
ActiveSheet.Name = r.Value
End If
End If
Next r
End With
Application.Goto Worksheets("Sheet1").Range("A1"), Scroll:=True
Application.ScreenUpdating = True

becky halsey
03-03-2016, 11:08 AM
It will not let me open the attachment. Can I just paste the code in my saved workbook?




You have a lot of blank row at the bottom of the table. Column X appears to be empty in those row, but they aren't, the cells appear to have spaces in them. This will cause issues, so those blank rows need to be deleted as I have done in the sample workbook.

There is a 31 character limit for a sheet name. Your values in the x column start with a number and then there are some spaces before the name. I wrote code that will cut off the last characters if needed to fit into the limit, so if you have any sheets with the name cut off it is because of a long name that combined with the spaces after the number have gone over the limit. I don't think there are any in existence because originally I wrote the code without the limit and didn't get any errors.

The first time the code is run it's a little slow because there are no sheets and it has to create all of them. My concern with running the code each time and having it go through all the names checking to see if a sheet exists seems to be a non issue as it does this very fast.

Try out the macro, I have it set to run from control + q.

Here's the code and below that is your sample workbook with the blank rows removed and all the employee sheets deleted so you can see the code adding them all.


Sub NewSheets()
Dim r As Range
Application.ScreenUpdating = False
With Worksheets("Sheet1")
For Each r In .ListObjects("Table_CURRENT_EMPLOYEE_S__SSN").ListColumns("F27").DataBodyRange
If Not r.Value = Empty Then
If Not Evaluate("ISREF('" & r.Value & "'!A1)") Then
Worksheets("template").Copy After:=Worksheets(Sheets.Count)
If Len(r.Value) > 31 Then r.Value = Left(r.Value, 31)
ActiveSheet.Name = r.Value
End If
End If
Next r
End With
Application.Goto Worksheets("Sheet1").Range("A1"), Scroll:=True
Application.ScreenUpdating = True

becky halsey
03-03-2016, 11:13 AM
Never mind! Got it!

becky halsey
03-03-2016, 11:47 AM
That was exactly what I needed it to do. I just pasted the codes in my actual sheet and it worked like a charm!! All I need now is to know how you make it run using ctrl+Q!

What a lifesaver!!!!!

skywriter
03-03-2016, 12:17 PM
You just go to where your list of macros is and there's a button that says options and there's a place to enter a shortcut key to run it from if you so choose.

Regarding your question about the columns. You have a table and the column is labeled F27, so I used that column label in the code. If you add or delete other columns the code doesn't care it's looking for that column name so it doesn't matter what order the columns are in.

If you change the name of the column, then you need to change it in the code also.

Good Luck

becky halsey
03-03-2016, 12:31 PM
Got it! Thanks so much for you time & help. I really appreciate it!

Becky




You just go to where your list of macros is and there's a button that says options and there's a place to enter a shortcut key to run it from if you so choose.

Regarding your question about the columns. You have a table and the column is labeled F27, so I used that column label in the code. If you add or delete other columns the code doesn't care it's looking for that column name so it doesn't matter what order the columns are in.

If you change the name of the column, then you need to change it in the code also.

Good Luck

becky halsey
03-15-2016, 01:45 PM
Hi! It's me again! Just wondering if you know an excel formula to calculate the vacation days accumulated for each month based on a certain date. I have attached a sample file with the answers I need it to add.15654

Thanks!

skywriter
03-15-2016, 03:24 PM
I'm not so good with formulas.
I would suggest a new thread.
Good Luck.
:beerchug:

becky halsey
03-17-2016, 08:07 AM
I am doing another sheet just like the one you wrote the big code for. I need the same code. My only problem is, it keeps trying to pull information from the file named Thursday (that was the sample file we used). How can I paste that code into another file and make it work?

thanks!