Re: Create/Update sheets based on added/modified information on master sheet
I get Runtime Error 9 on the next 2 lines. Subscript out of range.
Thank you for your continued help with this. I really appreciate all the time you're putting into this for me.
Re: Create/Update sheets based on added/modified information on master sheet
Sorry, I can not replicate the error.
Can you upload the file?
Re: Create/Update sheets based on added/modified information on master sheet
Here you go. I deleted all the individual sheets and clicked update. It created the first one and then had a problem.
Re: Create/Update sheets based on added/modified information on master sheet
Yep!
I forgot to set ws to nothing
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)
If ws Is Nothing Then
Set ws = Sheets.Add(after:=Sheets(1))
ws.Name = sn
End If
On Error GoTo 0
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
Set ws = Nothing
Next
End With
End SubRe: Create/Update sheets based on added/modified information on master sheet
Works!! But now for a few questions:
1. Is it possible to make it generate the new sheets even if there are no 'x' marks for that person? Many of the names will not earn awards for quite a while.
2. Can we have the new sheets appear at the end of the list instead of the start as they do in this latest version? If not, I can just use the Sort function I already have, but that leads to another question:
3. I liked how you had the sheets corresponding to deleted names move to the front of the list. Can that happen again? But keep the deleted names separate from the other names? Or perhaps just rename the deleted sheets to [Last, First (deleted)] or something?
Re: Create/Update sheets based on added/modified information on master sheet
1) Possible
2) Possible
3) Do you mean, move the worksheet(s) that are not in the list to the front of the cadets?
Re: Create/Update sheets based on added/modified information on master sheet
3. What I meant was, one of the versions you posted moved deleted names to the front of the list, but after the cadet name. You uploaded the file DatabaseV3-Reviced.
i.e. if these are the sheets, it was [Cadets] , [Deleted Person 1] , [Deleted Person 2] , [First Name Alphabetically] ... [Last Name Alphabetically]
I think it would be better (and easier) though to just leave them in alphabetically order and use the sheet name [Last, First (deleted)] format for names that get deleted from the [Cadets] sheet.
Could you please make #1 and 3 happen? I'll use the sorter for #2 so it doesn't really matter where new sheets are added in the list.
Re: Create/Update sheets based on added/modified information on master sheet
OK
change to
Sub test()
Dim i As Long, ii As Long, x, sn As String, n As Long
Dim ws As Worksheet, mySheets()
With Sheets("Cadets").Range("a1").CurrentRegion.Resize(, 67)
For i = 3 To .Rows.Count
On Error Resume Next
sn = .Cells(i, 1).Value & ", " & .Cells(i, 2).Value
ReDim Preserve mySheets(i - 1)
mySheets(i - 1) = sn
Set ws = Sheets(sn)
If ws Is Nothing Then
Set ws = Sheets.Add(after:=Sheets(1))
ws.Name = sn
End If
On Error GoTo 0
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
Set ws = Nothing
Next
End With
MoveSheets mySheets
End Sub
and add
Private Sub MoveSheets(mySheet)
Dim ws As Worksheet, x
For Each ws In Sheets
x = Application.Match(ws.Name, mySheet, 0)
If IsError(x) Then
ws.Move after:=Sheets("Cadets")
Else
ws.Move after:=Sheets(Sheets.Count)
End If
Next
End SubNote; if the order that you wanted is opposite to the resulting order then swop the lines within If close.
Re: Create/Update sheets based on added/modified information on master sheet
Or like this ?
Deleted names come right after the "Cadets", then sort the sheets of existing names?
Private Sub MoveSheets(mySheet)
Dim ws As Worksheet, x, n As Long
Dim i As Long, ii As Long
For Each ws In Sheets
x = Application.Match(ws.Name, mySheet, 0)
If IsError(x) Then
ws.Move after:=Sheets("Cadets")
n = 1
Else
ws.Move after:=Sheets(Sheets.Count)
End If
Next
If n > 0 Then
For i = n + 2 To Sheets.Count - 1
For ii = i + 1 To Sheets.Count
If Sheets(i).Name > Sheets(ii).Name Then
Sheets(ii).Move before:=Sheets(i)
End If
Next
Next
End If
End SubRe: Create/Update sheets based on added/modified information on master sheet
Okay, I think we've just about got it! I added a rename function to add (d) to the name of sheets for deleted people.
The code does something strange to the sorting order if the Cadets sheet is the only sheet when you click Update. All sheets are created, but I have no idea how it determines the order. However, clicking update again fixes the order.
The last thing I noticed is that if an 'x' is deleted, the corresponding Organization or Award isn't removed from a person's page. Is there an easy fix for that or would it be too much of a hassle?
Here is my current version. You can run it to see what exactly is happening. Try deleting someone's name from the Cadet sheet as well as removing some of the 'x' marks.
Re: Create/Update sheets based on added/modified information on master sheet
Can you replace "Get_Format" with the following,
Private Sub Get_Format(ByVal sn As String)
With Sheets(sn)
.Cells(5, "a").Value = "Organizations"
.Cells(6, "a").Resize(6).ClearContents
.Cells(5, "d").Value = "Awards"
.Cells(6, "d").Resize(6).ClearContents
.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 SubThen I just found a bug in "test"
Should be
13 should be 14 as column 13 is always blank.
Re: Create/Update sheets based on added/modified information on master sheet
What was your intention for changing "MoveSheets"?
Re: Create/Update sheets based on added/modified information on master sheet
I changed MoveSheets so that the sheets corresponding to deleted names have something that signifies them as removed from the list. Now not only are they at the front, they also have a "(d)" signifying deleted. I just commented it out because I now see that if the workbook is updated repeatedly, a new "(d)" will be added each time.
It appears that the sheets are sorted correctly with the exception of the last name on the list. The last name defaults to the first sheet after Cadets. The code also seems to only be able to detect and move one sheet at a time so if multiple people are deleted only one name is moved to the front. (I commented out my SortSheets function so that isn't moving stuff around prematurely.)
Re: Create/Update sheets based on added/modified information on master sheet
OK
If you have
"Cadets" - "Tom, ABC" - "Bill, DEF" - "Paul, GHI" - "Joe, JKL"
Then "Mark, MNO" is newly added and "Paul, GHI" is deleted from the list.
What order do you want after the sort?
Re: Create/Update sheets based on added/modified information on master sheet
I think I may have fixed it...?
Private Sub MoveSheets(mySheet)
Dim ws As Worksheet, x, n As Long
Dim i As Long, ii As Long
n = 0 ' I added this line because the test() function sets n=5
For Each ws In Sheets
x = Application.Match(ws.Name, mySheet, 0)
If IsError(x) Then
ws.Move after:=Sheets("Cadets")
n = n + 1 ' Changed this from n=1 to n=n+1
'If ws.Name <> "Cadets" Then ' Now how to I add a 'does not contain (d)' boolean operator?
'ws.Name = ws.Name & " (d)"
'End If
Else
ws.Move after:=Sheets(Sheets.Count)
End If
Next
If n > 0 Then
For i = n + 1 To Sheets.Count - 1 ' This was n+2 but n = count deleted people, then +1 for the Cadets sheet, right?
For ii = i + 1 To Sheets.Count
If Sheets(i).Name > Sheets(ii).Name Then
Sheets(ii).Move before:=Sheets(i)
End If
Next
Next
End If
End SubRe: Create/Update sheets based on added/modified information on master sheet
Quote from jindon;573316Display MoreOK
If you have
"Cadets" - "Tom, ABC" - "Bill, DEF" - "Paul, GHI" - "Joe, JKL"
Then "Mark, MNO" is newly added and "Paul, GHI" is deleted from the list.
What order do you want after the sort?
Should be:
"Cadets" - "Paul, GHI (d)" - "Bill, DEF" - "Joe, JKL" - "Mark, MNO" - "Tom, ABC"
Re: Create/Update sheets based on added/modified information on master sheet
Test this hard
Sub CreateCadetSheets()
Dim i As Long, ii As Long, x, sn As String, n As Long, temp As String
Dim ws As Worksheet, mySheets()
With Sheets("Cadets").Range("a1").CurrentRegion.Resize(, 67)
For i = 3 To .Rows.Count
sn = .Cells(i, 1).Value & ", " & .Cells(i, 2).Value
ReDim Preserve mySheets(i - 1)
mySheets(i - 1) = sn
temp = IsSheetExists(sn)
If temp <> "" Then
Set ws = Sheets(temp)
Else
Set ws = Sheets.Add(after:=Sheets(Sheets.Count))
End If
ws.Name = sn
ws.Cells(1).Resize(, 9).Value = _
.Rows(2).Resize(, 9).Value
ws.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
ws.Cells(n, "a").Value = _
.Rows(2).Cells(1, ii).Value
End If
Next
n = 5
For ii = 14 To 67
If .Rows(i).Cells(1, ii).Value = "x" Then
n = n + 1
ws.Cells(n, "d").Value = _
.Rows(2).Cells(1, ii).Value
End If
Next
Set ws = Nothing
Next
End With
MoveSheets mySheets
Sheets("Cadets").Select
End Sub
Function IsSheetExists(sn As String) As String
Dim ws As Worksheet
For Each ws In Sheets
If Trim(Replace(ws.Name, "(d)", "")) = sn Then
IsSheetExists = ws.Name
Exit For
End If
Next
End Function
Private Sub MoveSheets(mySheet)
Dim ws As Worksheet, x, n As Long, temp As String
Dim i As Long, ii As Long
For Each ws In Sheets
If ws.Name <> "Cadets" Then
temp = Trim(Replace(ws.Name, "(d)", ""))
x = Application.Match(temp, mySheet, 0)
If IsError(x) Then
ws.Move after:=Sheets("Cadets")
ws.Name = temp & " (d)"
n = 1
Else
ws.Name = temp
ws.Move after:=Sheets(Sheets.Count)
End If
End If
Next
For i = n + 2 To Sheets.Count - 1
For ii = i + 1 To Sheets.Count
If Sheets(i).Name > Sheets(ii).Name Then
Sheets(ii).Move before:=Sheets(i)
End If
Next
Next
End Sub
Private Sub Get_Format(ByVal sn As String)
With Sheets(sn)
.Cells(5, "a").Value = "Organizations"
.Cells(6, "a").Resize(6).ClearContents
.Cells(5, "d").Value = "Awards"
.Cells(6, "d").Resize(6).ClearContents
.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 SubRe: Create/Update sheets based on added/modified information on master sheet
This did it!!
I did make these changes to get it just right:
Private Sub MoveSheets(mySheet)
Dim ws As Worksheet, x, n As Long, temp As String
Dim i As Long, ii As Long
n = 0 'added this line
For Each ws In Sheets
If ws.Name <> "Cadets" Then
temp = Trim(Replace(ws.Name, "(d)", ""))
x = Application.Match(temp, mySheet, 0)
If IsError(x) Then
ws.Move after:=Sheets("Cadets")
ws.Name = temp & " (d)"
n = n + 1 ' made this n+1
Else
ws.Name = temp
ws.Move after:=Sheets(Sheets.Count)
End If
End If
Next
For i = n + 2 To Sheets.Count 'removed the -1
For ii = i + 1 To Sheets.Count
If Sheets(i).Name > Sheets(ii).Name Then
Sheets(ii).Move before:=Sheets(i)
End If
Next
Next
End SubRe: Create/Update sheets based on added/modified information on master sheet
Here is the final version. Thank you for sticking to it and helping me SO much these past few days!
Don’t have an account yet? Register yourself now and be a part of our community!