Create/Update sheets based on added/modified information on master sheet

wizcreations · Aug 22nd 2011

I do not know VBA yet I've been asked by my commanding officer to create this file from scratch. I've been trying to learn but I could really use a lot of help! Please see attachment.

I have a master sheet that lists people and info about each person. Each person has his/her own sheet that lists the same info from the master list. The list will be updated regularly and I need each individual sheets to update based on the master sheet.

The sheet "Cadets" is the list of names. My macro currently can detect new names and copy the information from columns A:I about that person to a new sheet with that person's name. I still need it to be able to detect changes to existing names and update the info as well as detect deleted names and delete the corresponding sheet.

Columns J:BP are possible attributes each person could have indicated by an 'x' in the column. I need the macro to detect an x and put the appropriate attribute into a list on that person's page. These lists also need to update with changes.

The sheet "delta, charlie" is an example of what a person's page should look like. The titles in cells A5, D5, I5, and A12 could all be created when a new sheet is created, but I don't know how to do that. The various lists from possible attributes will populate under those titles. The lists with the word "Additional" in the title will be updated manually for each person.

Smallman · Aug 23rd 2011

Re: Create/Update sheets based on added/modified information on master sheet

Hi wizcreations

The attached will do what you are looking for. It is not how I would want from scratch as I have duplicated the award coding to get it working. However it goes OK. Let us know if you have further enhancements in mind or it is not performing as expected.

Take care

Smallman

wizcreations · Aug 24th 2011

Re: Create/Update sheets based on added/modified information on master sheet

Thank you for this. I will be able to look at it tomorrow to see how it runs. Why do you say it is not how you would do it? What would you change?

jindon · Aug 24th 2011

Re: Create/Update sheets based on added/modified information on master sheet

Try this one
It will add a sheet for all the names in col.A+B

Code

Sub test()
    Dim i As Long, ii As Long, x, sn As String, n As Long
    With Sheets("Cadets").Range("a1").CurrentRegion.Resize(, 67)
        For i = 3 To .Rows.Count
            x = Application.CountIf(.Rows(i), "x")
            If x > 0 Then
                On Error Resume Next
                Application.DisplayAlerts = False
                sn = .Cells(i, 1).Value & ", " & .Cells(i, 2).Value
                Sheets(sn).Delete
                On Error GoTo 0
                Sheets.Add(after:=Sheets(1)).Name = sn
                Sheets(sn).Cells(1).Resize(, 9).Value = _
                .Rows(2).Resize(, 9).Value
                Sheets(sn).Cells(2, 1).Resize(, 9).Value = _
                .Rows(i).Resize(, 9).Value
                Get_Format sn
                n = 5
                For ii = 10 To 12
                    If .Rows(i).Cells(1, ii).Value = "x" Then
                        n = n + 1
                        Sheets(sn).Cells(n, "a").Value = _
                        .Rows(2).Cells(1, ii).Value
                    End If
                Next
                n = 5
                For ii = 13 To 67
                    If .Rows(i).Cells(1, ii).Value = "x" Then
                        n = n + 1
                        Sheets(sn).Cells(n, "d").Value = _
                        .Rows(2).Cells(1, ii).Value
                    End If
                Next
            End If
        Next
    End With
End Sub




Private Sub Get_Format(ByVal sn As String)
With Sheets(sn)
    .Cells(5, "a").Value = "Organizations"
    .Cells(5, "d").Value = "Awards"
    .Cells(5, "i").Value = "Additional Awards"
    .Cells(12, "a").Value = "Additional Extra Curriculars"
    .Range("a1:i1,a5,d5,a12,i5").Font.Bold = True
End With
End Sub

Display More

Smallman · Aug 24th 2011

Re: Create/Update sheets based on added/modified information on master sheet

Hi wizcreatioins

If I had the time to develop this from scratch I would have developed the code so whether there is a sheet existing or a new one is being created that the code to assign awards gets called once, not twice as I have it. It is just more efficient that way. It works ok though and Jindon has supplied some coding that looks good. Good luck with your project.

Take care

Smallman

wizcreations · Aug 25th 2011

Re: Create/Update sheets based on added/modified information on master sheet

This attached file includes my modifications.

Smallman,

I'm not entirely sure I understand how you'd have it call the sheet only once. I see how that is more efficient, but I can't really figure out how this is working with my limited VBA knowledge. At this point, if it works, that's fine by me! I have reviewed the file you uploaded and here are my comments:

1. I added code to make the titles bold on each person's page
2. Can it be made to detect deleted 'x' and update the lists? I noticed that it will overwrite old values but if there are now fewer boxes marked with an x there will be duplicates. I'm not sure how it's running now, but perhaps it'd be better to clear the contents and repopulate the list each time?
3. Can it be made to detect deleted people and delete their corresponding sheet?
4. There is some sub functions commented out, do those need to stay?

That's all I noticed that I feel needs changing. Overall this is fantastic! Thank you so much!

jindon,

Does this replace some of the code supplied by Smallman? Should I put this in and replace some of the older code or should I use what Smallman provided. I have no clue how to tell which will work better.

jindon · Aug 25th 2011

Re: Create/Update sheets based on added/modified information on master sheet

Try this one

wizcreations · Aug 25th 2011

Re: Create/Update sheets based on added/modified information on master sheet

Interesting. This version moves the deleted names to the front of the list. I suppose that works and the boss can just delete those sheets manually. Thanks for all your help both of you!

Also, I'm open to improvements so please let me know if you have any ideas to make this more functional.

jindon · Aug 25th 2011

Re: Create/Update sheets based on added/modified information on master sheet

If you want to delete the sheet(s) of deleted name(s)

try and be careful that the sheet name must be exact match.

Code

Sub test()
    Dim i As Long, ii As Long, x, sn As String, n As Long
    Del_Sheets ("Cadets")
    With Sheets("Cadets").Range("a1").CurrentRegion.Resize(, 67)
        For i = 3 To .Rows.Count
            x = Application.CountIf(.Rows(i), "x")
            If x > 0 Then
                On Error Resume Next
                Application.DisplayAlerts = False
                sn = .Cells(i, 1).Value & ", " & .Cells(i, 2).Value
                Sheets(sn).Delete
                On Error GoTo 0
                Sheets.Add(after:=Sheets(Sheets.Count)).Name = sn
                Sheets(sn).Cells(1).Resize(, 9).Value = _
                .Rows(2).Resize(, 9).Value
                Sheets(sn).Cells(2, 1).Resize(, 9).Value = _
                .Rows(i).Resize(, 9).Value
                Get_Format sn
                n = 5
                For ii = 10 To 12
                    If .Rows(i).Cells(1, ii).Value = "x" Then
                        n = n + 1
                        Sheets(sn).Cells(n, "a").Value = _
                        .Rows(2).Cells(1, ii).Value
                    End If
                Next
                n = 5
                For ii = 13 To 67
                    If .Rows(i).Cells(1, ii).Value = "x" Then
                        n = n + 1
                        Sheets(sn).Cells(n, "d").Value = _
                        .Rows(2).Cells(1, ii).Value
                    End If
                Next
            End If
        Next
    End With
End Sub
 
 
Private Sub Get_Format(ByVal sn As String)
    With Sheets(sn)
        .Cells(5, "a").Value = "Organizations"
        .Cells(5, "d").Value = "Awards"
        .Cells(5, "i").Value = "Additional Awards"
        .Cells(12, "a").Value = "Additional Extra Curriculars"
        .Range("a1:i1,a5,d5,a12,i5").Font.Bold = True
    End With
End Sub




Private Sub Del_Sheets(ByVal sn As String)
Dim ws As Worksheet
On Error Resume Next
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Sheets
    If ws.Name <> sn Then ws.Delete
Next
End Sub

Display More

wizcreations · Aug 25th 2011

Re: Create/Update sheets based on added/modified information on master sheet

Got it. Is everything I need all in Module 3?

jindon · Aug 25th 2011

Re: Create/Update sheets based on added/modified information on master sheet

Yup.

wizcreations · Aug 25th 2011

Re: Create/Update sheets based on added/modified information on master sheet

Jindon,
I realized that the version you provided overwrites each sheet and therefore removes the manually entered data under the Additional Awards and Additional Extra Curricular categories.

After additional review, I think you're right about not wanting to delete sheets. If someone's named gets changed, all their information might accidentally be deleted.

jindon · Aug 25th 2011

Re: Create/Update sheets based on added/modified information on master sheet

So, how do you want it ?

If you don't want to delete the existing sheet(s) those names are deleted from the list, just delete one line.

Code

Dim i As Long, ii As Long, x, sn As String, n As Long 
    Del_Sheets ("Cadets")  '<- delete this line
    With Sheets("Cadets").Range("a1").CurrentRegion.Resize(, 67)

wizcreations · Aug 25th 2011

Re: Create/Update sheets based on added/modified information on master sheet

Thanks. I wasn't sure which line to delete. What do I modify to make it not overwrite the manual entries?

jindon · Aug 26th 2011

Re: Create/Update sheets based on added/modified information on master sheet

I don't understand "manual entry".

Can you explain about it ?

wizcreations · Aug 26th 2011

Re: Create/Update sheets based on added/modified information on master sheet

Quote from wizcreations;572730

The lists with the word "Additional" in the title will be updated manually for each person.

If I enter info under the titles Additional Awards or Additional Extra Curriculars, I don't want that information to be erased each time the document is updated.

jindon · Aug 26th 2011

Re: Create/Update sheets based on added/modified information on master sheet

I see...
try this one

Code

Sub test()
    Dim i As Long, ii As Long, x, sn As String, n As Long
    Dim ws As Worksheet
    With Sheets("Cadets").Range("a1").CurrentRegion.Resize(, 67)
        For i = 3 To .Rows.Count
            x = Application.CountIf(.Rows(i), "x")
            If x > 0 Then
                On Error Resume Next
                sn = .Cells(i, 1).Value & ", " & .Cells(i, 2).Value
                Set ws = Sheets(sn)
                On Error GoTo 0
                If ws Is Nothing Then
                    Set ws = Sheets.Add(after:=Sheets(1)).Name = sn
                End If
                Sheets(sn).Cells(1).Resize(, 9).Value = _
                .Rows(2).Resize(, 9).Value
                Sheets(sn).Cells(2, 1).Resize(, 9).Value = _
                .Rows(i).Resize(, 9).Value
                Get_Format sn
                n = 5
                For ii = 10 To 12
                    If .Rows(i).Cells(1, ii).Value = "x" Then
                        n = n + 1
                        Sheets(sn).Cells(n, "a").Value = _
                        .Rows(2).Cells(1, ii).Value
                    End If
                Next
                n = 5
                For ii = 13 To 67
                    If .Rows(i).Cells(1, ii).Value = "x" Then
                        n = n + 1
                        Sheets(sn).Cells(n, "d").Value = _
                        .Rows(2).Cells(1, ii).Value
                    End If
                Next
            End If
        Next
    End With
End Sub
 
 
Private Sub Get_Format(ByVal sn As String)
    With Sheets(sn)
        .Cells(5, "a").Value = "Organizations"
        .Cells(5, "d").Value = "Awards"
        .Cells(5, "i").Value = "Additional Awards"
        .Cells(12, "a").Value = "Additional Extra Curriculars"
        .Range("a1:i1,a5,d5,a12,i5").Font.Bold = True
    End With
End Sub

Display More

wizcreations · Aug 26th 2011

Re: Create/Update sheets based on added/modified information on master sheet

Code

If ws Is Nothing Then
                    Set ws = Sheets.Add(after:=Sheets(1)).Name = sn
                End If

this causes 'Runtime Error 424.' ???

wizcreations · Aug 26th 2011

Re: Create/Update sheets based on added/modified information on master sheet

It seems that the script does not add sheets if there are no 'x' marks in any of the options. Is that what this part of the code is doing?

Code

x = Application.CountIf(.Rows(i), "x")
If x > 0 Then

jindon · Aug 26th 2011

Re: Create/Update sheets based on added/modified information on master sheet

Can you just change

Code

If ws Is Nothing Then 
    Set ws = Sheets.Add(after:=Sheets(1)).Name = sn 
End If

to

Code

If ws Is Nothing Then
                    Set ws = Sheets.Add(after:=Sheets(1))
                    ws.Name = sn
                End If

Create/Update sheets based on added/modified information on master sheet

Participate now!

Share

Users Viewing This Thread