At my wits end

[elsone31]

I have been trying to get this worked out for almost a week now and it is time to turn to those greater than I am. I am attaching my workbook as well.

I have this code that does a great job moving the data to the template and saving to the directory. Turned days worth of manual copy paste into a mere 15 minutes. Two issues I can not get to work. 1.Getting the buttons for sorting the periods to re-assign to the the new workbook, 2. using my hide empty columns macro to work. the reason I believe is because I just had the code to write our 40 columns of data regardless of how many students were in each period. I did this because the sort buttons are hard coded for those cell ranges. Any help is GREATLY appreciated. I am using Excel 2010.

Sub create_file()
Dim student_array(82, 39) As Variant
Dim eof_flag As String
Dim eoc_flag As String
Dim eot_flag As String
Dim t_name As String
Dim s_nbr As Long
Dim c_nbr As String
Dim per As Integer
Dim i As Integer
Dim j As Integer
Dim i2 As Integer
Dim i3 As Integer
Dim j2 As Integer
Dim tchr_tab_row As Integer
Dim data_tab_col As Integer
Dim per_col As Integer
Dim max_row As Integer
eof_flag = "no"
eoc_flag = "no"
eot_flag = "no"
Dim FileName As Variant
Dim Campus As String


'
'get a teacher name
'
Worksheets("teacher tab").Select
teacher_tab_row = 1
'max_row has maximum number of rows on data tab
max_row = 82
c_nbr = Cells(teacher_tab_row, 1)
t_name = Cells(teacher_tab_row, 2)




Do While eof_flag <> "yes"
   Worksheets("data tab").Select
   i = 1
Worksheets("teacher tab").Select
Campus_Name = Cells(teacher_tab_row, 3)
File_Name = Cells(teacher_tab_row, 4)


 Worksheets("data tab").Select
   i = 1
'
' look for teacher in data tab
'
   Do While eot_flag <> "yes"
      If (((t_name = Cells(2, i)) And (c_nbr = Cells(4, i))) Or (Cells(2, i) = "")) Then
         eot_flag = "yes"
      Else
         i = i + 1
      End If
   Loop
'
   If Cells(2, i) <> "" Then
      eot_flag = "found"
   End If
'
'teacher has been found
'
   data_tab_col = i
   template_tab_col = 6
   j2 = data_tab_col
   per = Cells(3, j2)
   Do While eot_flag = "found"
   
'
'zero out array to prepare for data
'
      GoSub zero_out_array
'
'this is a nested loop for reading in the data from the data tab
'
      For j = 1 To 39
'
'keep moving data into array as long as the teacher and the period and the campus is the same
'     otherwise, jump out
'
           If (Cells(2, j2) = t_name And Cells(4, j2) = c_nbr And Cells(3, j2) = per) Then
              i2 = 1
              For i = 1 To max_row
                 student_array(i, j) = Cells(i2, j2)
                 i2 = i2 + 1
              Next
                 j2 = j2 + 1
           Else
              j = 40
           End If
      Next
'
'here is where we will move the data to the new sheet
'
      Worksheets("template tab").Select
      per_col = template_tab_col
      For j = 1 To 39
          i2 = 1
          For i = 1 To max_row
              Cells(i2, per_col) = student_array(i, j)
              i2 = i2 + 1
          Next
              per_col = per_col + 1
       Next
'
'if the teacher has another period, then switch to new period
'
       Worksheets("data tab").Select
       If Cells(2, j2) = t_name Then
          per = Cells(3, j2)
          template_tab_col = template_tab_col + 40
       Else
          eot_flag = "no"
       End If
Loop
'
'get another teacher name
'
'this is where we will write out the template to finish off the teacher
Worksheets("template tab").Select
ActiveSheet.Copy


Dim Path As String
Campus = Campus_Name


Path = "Q:\ASSESSMENT\MIDDLE SCHOOLS\" & Campus & "\STUDENT PERFORMANCE TRACKERS\2016-2017\"
FileName = FileName_File_Name
ActiveWorkbook.SaveAs FileName:="Q:\ASSESSMENT\MIDDLE SCHOOLS\" & Campus & "\STUDENT PERFORMANCE TRACKERS\2016-2017\ " & File_Name & ".xlsm", FileFormat:=52


ActiveWorkbook.Close SaveChanges:=True


GoSub blank_template_sheet
'
        GoSub search_tchr_tab
        If eof_flag <> "yes" Then
           eot_flag = "no"
        End If
'
Loop
Exit Sub
search_tchr_tab:
Worksheets("teacher tab").Select
teacher_tab_row = teacher_tab_row + 1
c_nbr = Cells(teacher_tab_row, 1)
t_name = Cells(teacher_tab_row, 2)
If c_nbr = "" Then
   eof_flag = "yes"
End If


Return
zero_out_array:
   For j = 1 To 39
       For i = 1 To max_row
           student_array(i, j) = ""
       Next
   Next
Return
blank_template_sheet:
Worksheets("template tab").Select
    Range("F1:AR100").ClearContents
    Range("AT1:CF100").ClearContents
    Range("CH1:DT100").ClearContents
    Range("DV1:FH100").ClearContents
    Range("FJ1:GV100").ClearContents
    Range("GX1:IK100").ClearContents
Return
End Sub

Code for sorting this works fine, but wanted to share as this is the code connected to the buttons.Practice.xlsb

Sub Button1_Click()
Dim myvalue As Variant


'Dim myRng As Range
'
'Use an Input box to select the row to sort
      myvalue = InputBox("Row Number")
    ActiveSheet.Columns("F:AS").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add(Range("F" & myvalue & ":AS" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
        199, 206)
    ActiveSheet.Sort.SortFields.Add(Range("F" & myvalue & ":AS" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 208, 59)
      'myvalue = InputBox("Row Number")
    ActiveSheet.Sort.SortFields.Add(Range("F" & myvalue & ":AS" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 255, 147)
    With ActiveSheet.Sort
      'myvalue = InputBox("Row Number")
    ActiveSheet.Sort.SortFields.Add(Range("F" & myvalue & ":AS" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(220, _
        230, 241)
       'myvalue = InputBox("Row Number")
    ActiveSheet.Sort.SortFields.Add(Range("F" & myvalue & ":AS" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(206, _
        254, 206)
        .SetRange ActiveSheet.Range("F1:AS95")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlSortRows
        .Apply
    End With


End Sub

Sub Button2_Click()
Dim myvalue As Variant


'Dim myRng As Range
'
'Use an Input box to select the row to sort
      myvalue = InputBox("Row Number")
    ActiveSheet.Columns("AT:CG").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add(Range("AT" & myvalue & ":CG" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
        199, 206)
     ActiveSheet.Sort.SortFields.Add(Range("AT" & myvalue & ":CG" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 208, 59)
      'myvalue = InputBox("Row Number")
    ActiveSheet.Sort.SortFields.Add(Range("AT" & myvalue & ":CG" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 255, 147)
    With ActiveSheet.Sort
      'myvalue = InputBox("Row Number")
     ActiveSheet.Sort.SortFields.Add(Range("AT" & myvalue & ":CG" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(220, _
        230, 241)
       'myvalue = InputBox("Row Number")
    ActiveSheet.Sort.SortFields.Add(Range("AT" & myvalue & ":CG" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(206, _
        254, 206)
        .SetRange ActiveSheet.Range("AT1:CG95")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlSortRows
        .Apply
    End With
End Sub

Sub Button3_Click()
Dim myvalue As Variant


'Dim myRng As Range
'
'Use an Input box to select the row to sort
      myvalue = InputBox("Row Number")
    ActiveSheet.Columns("CH:DU").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add(Range("CH" & myvalue & ":DU" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
        199, 206)
     ActiveSheet.Sort.SortFields.Add(Range("CH" & myvalue & ":DU" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 208, 59)
      'myvalue = InputBox("Row Number")
    ActiveSheet.Sort.SortFields.Add(Range("CH" & myvalue & ":DU" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 255, 147)
    With ActiveSheet.Sort
      'myvalue = InputBox("Row Number")
    ActiveSheet.Sort.SortFields.Add(Range("CH" & myvalue & ":DU" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(220, _
        230, 241)
       'myvalue = InputBox("Row Number")
    ActiveSheet.Sort.SortFields.Add(Range("CH" & myvalue & ":DU" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(206, _
        254, 206)
        .SetRange ActiveSheet.Range("CH1:DU95")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlSortRows
        .Apply
    End With
End Sub

Sub Button4_Click()
Dim myvalue As Variant


'Dim myRng As Range
'
'Use an Input box to select the row to sort
      myvalue = InputBox("Row Number")
    ActiveSheet.Columns("DV:FI").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add(Range("DV" & myvalue & ":FI" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
        199, 206)
     ActiveSheet.Sort.SortFields.Add(Range("DV" & myvalue & ":FI" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 208, 59)
      'myvalue = InputBox("Row Number")
    ActiveSheet.Sort.SortFields.Add(Range("DV" & myvalue & ":FI" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 255, 147)
    With ActiveSheet.Sort
      'myvalue = InputBox("Row Number")
    ActiveSheet.Sort.SortFields.Add(Range("DV" & myvalue & ":FI" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(220, _
        230, 241)
       'myvalue = InputBox("Row Number")
    ActiveSheet.Sort.SortFields.Add(Range("DV" & myvalue & ":FI" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(206, 254, 206)
        .SetRange ActiveSheet.Range("DV1:FI95")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlSortRows
        .Apply
    End With
End Sub

Sub Button5_Click()
Dim myvalue As Variant


'Dim myRng As Range
'
'Use an Input box to select the row to sort
      myvalue = InputBox("Row Number")
    ActiveSheet.Columns("FJ:GW").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add(Range("FJ" & myvalue & ":GW" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
        199, 206)
     ActiveSheet.Sort.SortFields.Add(Range("FJ" & myvalue & ":GW" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 208, 59)
      'myvalue = InputBox("Row Number")
    ActiveSheet.Sort.SortFields.Add(Range("FJ" & myvalue & ":GW" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 255, 147)
    With ActiveSheet.Sort
      'myvalue = InputBox("Row Number")
     ActiveSheet.Sort.SortFields.Add(Range("FJ" & myvalue & ":GW" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(220, _
        230, 241)
       'myvalue = InputBox("Row Number")
    ActiveSheet.Sort.SortFields.Add(Range("FJ" & myvalue & ":GW" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(206, _
        254, 206)
        .SetRange ActiveSheet.Range("FJ1:GW95")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlSortRows
        .Apply
    End With
End Sub

Sub Button6_Click()
Dim myvalue As Variant


'Dim myRng As Range
'
'Use an Input box to select the row to sort
      myvalue = InputBox("Row Number")
    ActiveSheet.Columns("GX:IK").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add(Range("GX" & myvalue & ":IK" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
        199, 206)
     ActiveSheet.Sort.SortFields.Add(Range("GX" & myvalue & ":IK" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 208, 59)
      'myvalue = InputBox("Row Number")
    ActiveSheet.Sort.SortFields.Add(Range("GX" & myvalue & ":IK" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 255, 147)
    With ActiveSheet.Sort
      'myvalue = InputBox("Row Number")
     ActiveSheet.Sort.SortFields.Add(Range("GX" & myvalue & ":IK" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(220, _
        230, 241)
       'myvalue = InputBox("Row Number")
    ActiveSheet.Sort.SortFields.Add(Range("GX" & myvalue & ":IK" & myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(206, _
        254, 206)
        .SetRange ActiveSheet.Range("GX1:GW95")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlSortRows
        .Apply
    End With
End Sub

[GTO]

Greetings elsone31,

I think you forgot to actually attach the workbook, which would probably be very helpful to us.

Mark

[SamT]

It's right above sub button1_click

[Paul_Hossler]

Yes, but there is no data on it, none of the referenced buttons are actually placed on a sheet, and I couldn't figure out which data should go where

Edit -- found the buttons -- there are no labels on any of them so I missed them

[elsone31]

Paul,

I am sorry about the missing data, but the file would not load with any of it in there no matter how I tried to reduce the size otherwise.

[Paul_Hossler]

I just think it'd be easier to analyze with some data

You have a Name COMBINE_SCORE with an external link that doesn't seem to be used. Unpacked it is 12MB, and packed (xlsm are really zip files) is 1.3MB

Capture.JPG

Deleting that, Practice.xlsm is now just 39kb, including the button macros

[elsone31]

new mac2 (3).xlsm

Ok here is the original file. Don't know what happened earlier

[Paul_Hossler]

OK, looking at it

I added the Button_ logic from post #1 since it looks like you only posted some of the code in #7

Few comments

1. Recommend you use Option Explicit at the top of each module

You Dim-ed tchr_tab_row, but used teacher_tab_row

Dim tchr_tab_row As Long

Worksheets("teacher tab").Select

teacher_tab_row = 1

2. You can simplify the Button Sort logic by putting all the common code into a sub and then calling that sub with appropriate parameters

Option Explicit

Private Sub pvtSort_Button_Click(R As Range)
    Dim myvalue As Long
    'Use an Input box to select the row to sort
    myvalue = InputBox("Row Number, or '0' to exit")
    
    If myvalue < 1 Then Exit Sub
    
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add(R.Rows(myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 199, 206)
        .SortFields.Add(R.Rows(myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 208, 59)
        .SortFields.Add(R.Rows(myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 255, 147)
        .SortFields.Add(R.Rows(myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(220, 230, 241)
        .SortFields.Add(R.Rows(myvalue), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(206, 254, 206)
        .SetRange R
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlSortRows
        .Apply
    End With
      
End Sub
 
 
Sub Button1_Click()
    Call pvtSort_Button_Click(ActiveSheet.Range("F1:AS95"))
End Sub
Sub Button2_Click()
    Call pvtSort_Button_Click(ActiveSheet.Range("AT1:CG95"))
End Sub
Sub Button3_Click()
    Call pvtSort_Button_Click(ActiveSheet.Range("CH1:DU95"))
End Sub
Sub Button4_Click()
    Call pvtSort_Button_Click(ActiveSheet.Range("DV1:FI95"))
End Sub
Sub Button5_Click()
    Call pvtSort_Button_Click(ActiveSheet.Range("FJ1:GW95"))
End Sub
Sub Button6_Click()
    Call pvtSort_Button_Click(ActiveSheet.Range("GX1:GW95"))
End Sub

3. When I ran 'create_file" this seems to clear the C3 : D7 on the template tab and I don't know if you want to do that

'here is where we will move the data to the new sheet
'
   Worksheets("template tab").Select
    per_col = j2
   For j = 1 To 40
       i2 = 1
       For i = 1 To max_row
           Cells(i2, per_col) = student_array(i, j)
           i2 = i2 + 1
       Next
           per_col = per_col + 1
   Next

4. There's nothing in the code that saves the data that I can see, but simply do a FileSaveAs and then opening that file, the button macros were runnable

[GTO]

It's right above sub button1_click

Well [bleep], I am obviously getting blind as a .

Thanks Sam

[snb]

Can you describe what should be the process and the result of the macro 'create_file', because it doesn't create a file, nor does it save anything to a directory.

I have this code that does a great job moving the data to the template and saving to the directory.

I fear you code can be reduced to some 12 lines of code.
You should always avoid 'select' in VBA, the use of goto statements, and the use of redundant variables.

[SamT]

@ Paul,

There's nothing in the code that saves the data that I can see, but simply do a FileSaveAs and then opening that file, the button macros were runnable

       Worksheets("template tab").Select 
        ActiveSheet.Copy 
         
         
        Dim Path As String 
        Campus = Campus_Name 
         
         
        Path = "Q:\ASSESSMENT\MIDDLE SCHOOLS\" & Campus & "\STUDENT PERFORMANCE TRACKERS\2016-2017\" 
        FileName = FileName_File_Name 
        ActiveWorkbook.SaveAs FileName:="Q:\ASSESSMENT\MIDDLE SCHOOLS\" & Campus & "\STUDENT PERFORMANCE TRACKERS\2016-2017\ " & File_Name & ".xlsm", FileFormat:=52 
         
         
        ActiveWorkbook.Close SaveChanges:=True

[SamT]

Two issues I can not get to work. 1.Getting the buttons for sorting the periods to re-assign to the the new workbook

Move all Button Click code to Template sheet and Reassign button Macros.

[Paul_Hossler]

@ Paul,

       Worksheets("template tab").Select 
        ActiveSheet.Copy 
         
         
        Dim Path As String 
        Campus = Campus_Name 
         
         
        Path = "Q:\ASSESSMENT\MIDDLE SCHOOLS\" & Campus & "\STUDENT PERFORMANCE TRACKERS\2016-2017\" 
        FileName = FileName_File_Name 
        ActiveWorkbook.SaveAs FileName:="Q:\ASSESSMENT\MIDDLE SCHOOLS\" & Campus & "\STUDENT PERFORMANCE TRACKERS\2016-2017\ " & File_Name & ".xlsm", FileFormat:=52 
         
         
        ActiveWorkbook.Close SaveChanges:=True

None of that is in the file in #7

At My Wits End

new mac2 (3).xlsm

Ok here is the original file. Don't know what happened earlier

I'm a-feared we're having MAJOR CM issues, and it's a rapidly moving target

[SamT]

See Post #1

[Paul_Hossler]

Which file is anyone to look at?

It's asking a lot to have people guess which pieces of code from posts and multiple versions of similar files to use

I had to pull the 6 button click macro code from the posts to the put in the workbook in #7 (new mac2(3).xlsm), which had a different version of 'create_file' that post #1 (practice.xlsb)

The #7 file has no saving logic in it

It's difficult to interpret someone else coding style as it is;

e.g. there are variables called

a. teacher_tab_row and tchr_tab_row

    Dim tchr_tab_row As Integer
     '
     'get a teacher name
     '
    Worksheets("teacher tab").Select
    teacher_tab_row = 1

b. FileName, File_Name, and FileName_File_Name

        Dim Path As String
        Campus = Campus_Name
         
         
        Path = "Q:\ASSESSMENT\MIDDLE SCHOOLS\" & Campus & "\STUDENT PERFORMANCE TRACKERS\2016-2017\"
        FileName = FileName_File_Name
        ActiveWorkbook.SaveAs FileName:="Q:\ASSESSMENT\MIDDLE SCHOOLS\" & Campus & "\STUDENT PERFORMANCE TRACKERS\2016-2017\ " & File_Name & ".xlsm", FileFormat:=52

c. I haven't seen GoSub used since HP Basic in the '70's so it took me a while to trace though that

d. The Data sheet seems to have one student (Johnson S.) but a lot of numbers. No idea what they're for


>>>>>What would be helpful is for the OP to provide the latest and final-est workbook complete with ALL the macros and enough sample data or all types to test with, along with a summary of what they're trying to do

I'm guessing that as input this master WB will be populated with a list of teachers, the classes and campus where they teach, and the student names that take each class. The output is intended to be a series of teacher-specific workbooks, one / teacher with all the classes they teach and the students in each class to capture grades, etc. Each of the output workbooks to have some sorting or other functionality

[elsone31]

New File.xlsb

Wow Paul thank you for cleaning up my sort button macros! I changed the dim statment.

I want to apologize for sending you and older version of the code. I was at home and did not realize it til I got your response. This one will not clear those ranges stated in your response (I hope). The new file has the latest version that I am using and hopefully it will show how the sort buttons stay linked to the original file.

I am also including a macro I found on line to help revome the external links but I can't get it to work or place it in a place in my own macro that will work.

I put them in the (template tab) as that is the one I copy out to make a new workbook. I really appreciate your help.

[Paul_Hossler]

I think you're over thinking this

I really don't follow the logic of populating the data but if it were me, I'd leave the 'master' template sheet alone (the sort macros are in that sheet's module)

Make a copy of the template sheet, put the data on that one, and then copy the copy with data to a new WB, and save that one (example is the sub CopyAndSave below and in Master_v1)

It's easy enough to include other worksheets for different classes by that teacher in his/her/their workbook

I'm not sure how you're populating the data (did not bother to trace the logic), but I think / feel that you're making a lot of work for yourself and that the code be greatly simplified

Option Explicit

Sub CopyAndSave()
    Dim Campus_Number As String
    Dim Teacher_Name As String
    Dim Campus_Name As String
    Dim Path_Name As String
    Dim File_Name As String
    Dim wsTemplate As Worksheet
    Dim wsTeacher As Worksheet
    Dim sTeacherSheet As String

    Set wsTemplate = Worksheets("template tab")
    Campus_Number = "41"
    Teacher_Name = "Smith"
    Campus_Name = "Campus1"
    File_Name = "6th Smith" & ".xlsm"
    sTeacherSheet = Campus_Name & "-" & File_Name

    'delete sheet if it exists
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets(sTeacherSheet).Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    'copy empty template
    wsTemplate.Copy after:=wsTemplate
    Set wsTeacher = ActiveSheet
    wsTeacher.Name = sTeacherSheet
    
    'do all work on the copy - just exampe of putting data there
    With wsTeacher
        .Range("F1") = "Student1234"
        .Range("F2") = Teacher_Name
        .Range("F3") = "6th"
        .Range("F4") = Campus_Name
    End With
    
    'write the copy to new WB and save and close -- folder must exist
    wsTeacher.Move
    
    Path_Name = Environ("TEMP") & "\" & Campus_Name & "\STUDENT PERFORMANCE TRACKERS\2016-2017\"
    
    'delete workbook  if it exists
    On Error Resume Next
    Application.DisplayAlerts = False
    Kill Path_Name & File_Name
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    ActiveWorkbook.SaveAs FileName:=Path_Name & File_Name, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    ActiveWindow.Close
    ThisWorkbook.Activate
 
End Sub

In Master_v1 I have the sort logic on the template tab code module, and your convert logic in a standard module (it really shouldn't be in ThisWorkbook)

Running just my CopyAndSave macro which creates a copy of template tab, put some data on it, and then saves as a new wb generated the second workbook

[elsone31]

Paul,

A friend is teaching me to write code and helped me write this one. She is old school and that is how I am learning. She uses GoSubs so I used them. So I know how this one works because I was a part of writing it, I as I get better I hope to be able to be a more concise and up to date code writer. As far as the current way the code is written, it purpose and the final output, My data set consists of 10 different campus numbers, with up to 6 different teachers per campus with each teacher have up to 6 different periods with each student having as many as 70 rows of data.
The data gets read into an array and is stored with the following piece of the code up to 40 students so I could hard code the sort ranges.

'keep moving data into array as long as the teacher and the period and the campus is the same
' otherwise, jump out
'
If (Cells(2, j2) = t_name And Cells(4, j2) = c_nbr And Cells(3, j2) = per) Then
i2 = 1
For i = 1 To max_row
student_array(i, j) = Cells(i2, j2)
i2 = i2 + 1
Next
j2 = j2 + 1
Else
j = 40
End If
Next

It then reads it into the template using this part of the code. Works great. Reads all my data in and out in less than six minutes.

Worksheets("template tab").Select
per_col = template_tab_col
For j = 1 To 39
i2 = 1
For i = 1 To max_row
Cells(i2, per_col) = student_array(i, j)
i2 = i2 + 1
Next
per_col = per_col + 1
Next

It then copies the tab out as a new workbook and saves to the directory with the file name goes back through and clears the array, finds the next teacher and continues this loop for all campuses, teachers,periods and student data. My ONLY problem is that is saves the new workbook with the sort buttons having external links to the original workbook. I know I have been all over the place in trying to explain and share my code and workbooks with everyone, but not intentionally and I appreciate the suggestions I have gotten so far and will work to incorporate the new knowledge into the code, but as for now it works and does what I need except for allowing it to save and letting the sort button have reference in the new saved workbook .

[elsone31]

snb,

I am a code writer in training with a co-worker who learned it this way and teaches it to me this way we wrote this together and I understand it for that reason only as far as the complexity of it. I am sorry about the way I originally posted. I explained the data set and the input and output a little better in a recent post. As you will see even though not written in the most conventional way it does do what I need it to do EXCEPT in need to break the external link references and have the buttons linked to the code within the NEW workbook. Any help with getting that kink worked out would be greatly appreciated. I will also start using the goto statements.

[SamT]

My ONLY problem is that is saves the new workbook with the sort buttons having external links to the original workbook

Delete those antique "Forms" style Buttons and replace them with new and improved "Control Toolbox" style Command Buttons.

Right Click each CommandButton and choose "View Code" to create new CommandButton_Click Event Subs.

Put the Original sort code into the new CommandButton_Click() Event Subs that were auto created in the Template Sheet's Code page. Note that if you use the Control's Property sub-menu to change the names before "View Code", those names will be reflected in the Event subs. The Control Name and the Sub Name must match.

"Control Toolbox" Controls are identical to the Controls used when you create a UserForm. They and their code are integral to their container, whether copied or moved.

Sorry I didn't catch it sooner, but I quit using "Forms" controls a long time ago, they are a carry over from Excel 4.