PDA

View Full Version : [SOLVED] At my wits end



elsone31
08-31-2016, 05:52 PM
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.16983


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
09-01-2016, 09:55 AM
Greetings elsone31,

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

Mark

SamT
09-01-2016, 10:54 AM
It's right above sub button1_click

Paul_Hossler
09-01-2016, 12:39 PM
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
09-01-2016, 01:12 PM
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
09-01-2016, 03:39 PM
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

16998

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

elsone31
09-01-2016, 04:05 PM
17001

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

Paul_Hossler
09-01-2016, 05:08 PM
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
09-02-2016, 02:16 AM
It's right above sub button1_click

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

Thanks Sam :jsmile:

snb
09-02-2016, 02:48 AM
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
09-02-2016, 05:13 AM
@ 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
09-02-2016, 05:30 AM
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
09-02-2016, 06:26 AM
@ 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 (http://www.vbaexpress.com/forum/attachment.php?attachmentid=17001&d=1472771009)

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
09-02-2016, 06:31 AM
See Post #1

Paul_Hossler
09-02-2016, 07:01 AM
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
09-02-2016, 10:28 AM
17007

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
09-04-2016, 07:52 AM
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
09-04-2016, 03:06 PM
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
09-04-2016, 03:32 PM
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
09-04-2016, 05:33 PM
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.

Paul_Hossler
09-04-2016, 05:40 PM
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.

That's what I did for the OP in the 'template tab' when I generated 'Master_v1' in post #17. The generated separate 'teacher' workbook's buttons are not linked to the source workbook

elsone31
09-04-2016, 08:18 PM
Sam,

THANK YOU, so simple and works absolutely perfectly!!!! Woo hoo.