code to save sheets as separate word files

[palaeontology]

I have inherited a spreadsheet which has a number of userforms that have some reasonable coding in behind them.

One userform allows the user to click on which teacher they are, and the userform then populates showing which students belong in the class(es) that teacher teaches.

The user can then click either on the class heading or individual students in that class, and a button then creates a copy of a template sheet for each student, names it with the 5-digit student number for that student selected and populates it with the students' results.

The same button then prints each student profile to paper.

Once printed, the code then deletes those copies of the template sheet that were created.

This is the current code for all that ....

Private Sub CommandButton3_Click()Dim Addme As Range
Dim x As Integer
If IsEmpty(Sheets("PrintTemplate").Range("V49")) Then
    Set Addme = Sheets("PrintTemplate").Range("V49")
Else
    Set Addme = Sheets("PrintTemplate").Range("V" & Rows.Count).End(xlUp).Offset(1, 0)
End If
For x = 0 To Me.ListBox_1st_Class.ListCount - 1
If Me.ListBox_1st_Class.Selected(x) Then
        Addme = Me.ListBox_1st_Class.List(x)
        Addme.Offset(, 1).Value = Me.ListBox_1st_Class.List(x, 1)
        Set Addme = Addme.Offset(1, 0)
        End If
    Next x
For x = 0 To Me.ListBox_1st_Class.ListCount - 1
If Me.ListBox_1st_Class.Selected(x) Then Me.ListBox_1st_Class.Selected(x) = False
Next x


'###########
'Code2
' "Y49", Column "Y" &  "Me.ListBox_2nd_Class"


If IsEmpty(Sheets("PrintTemplate").Range("Y49")) Then
    Set Addme = Sheets("PrintTemplate").Range("Y49")
Else
    Set Addme = Sheets("PrintTemplate").Range("Y" & Rows.Count).End(xlUp).Offset(1, 0)
End If
For x = 0 To Me.ListBox_2nd_Class.ListCount - 1
If Me.ListBox_2nd_Class.Selected(x) Then
        Addme = Me.ListBox_2nd_Class.List(x)
        Addme.Offset(, 1).Value = Me.ListBox_2nd_Class.List(x, 1)
        Set Addme = Addme.Offset(1, 0)
        End If
    Next x
For x = 0 To Me.ListBox_2nd_Class.ListCount - 1
If Me.ListBox_2nd_Class.Selected(x) Then Me.ListBox_2nd_Class.Selected(x) = False
Next x




 
 
 'Copy Template Multiple Times and Rename them with names from a List
Dim ws As Worksheet, Ct As Long, c As Range
Set ws = Worksheets("Student Profile Template")
Application.ScreenUpdating = False
For Each c In Sheets("PrintTemplate").Range("AH49:AH100")
    If c.Value <> "" Then
        ws.Copy after:=Sheets(Sheets.Count)
        ActiveSheet.Name = c.Value
        Ct = Ct + 1
    End If
Next c
Application.ScreenUpdating = True






'Print all Sheets named with a 4-digit number
For Each ws In ThisWorkbook.Worksheets
    If ws.Name Like "#####" Then
        ws.Range("P22:U22").Font.Color = vbWhite
        ws.Range("P22:U22").Interior.Color = vbWhite
        ws.PageSetup.Orientation = xlLandscape
        ws.PrintOut From:=1, To:=1
    End If
Next ws




'Delete all Sheets named with a 4-digit number
  Application.DisplayAlerts = False
  For Each ws In Worksheets
    If ws.Name Like "#####" Then ws.Delete
  Next ws




'Clear student names from chosen list
Dim tbl As Range
Set tbl = Sheets("PrintTemplate").Range("V49:AF400")
tbl.ClearContents


End Sub

What I would like to do is to change the code (at the end) to (instead of printing to paper) save a word or pdf file (whichever is easier) of each of those students' sheets, that were created, to this location ... G:\Maths Dept\STUDENT RESULTS\2019\PDF Profile Copies

Would this be the only section of the code that needs to be changed ????

'Print all Sheets named with a 4-digit number
For Each ws In ThisWorkbook.Worksheets
    If ws.Name Like "#####" Then
        ws.Range("P22:U22").Font.Color = vbWhite
        ws.Range("P22:U22").Interior.Color = vbWhite
        ws.PageSetup.Orientation = xlLandscape
        ws.PrintOut From:=1, To:=1
    End If
Next ws

If so, is anyone able to help me change the code to what would be needed ..

Very kind regards,

Chris

 

[Macropod]

So which do you want - Word files or PDFs? You can create PDFs simply via Save As. For Word files, you need to automate Word, copy the content to it, etc. before saving in Word via Save As.

 

[palaeontology]

if it's simple to go to PDF, can we code it that way please ?

I appreciate any assistance at all,

very kind regards,

Chris

 

[Macropod]

How about looking at the Worksheet.ExportAsFixedFormat Method in the VBA help file...

 

[palaeontology]

Hi Macropod, as I mentioned, I've inherited this spreadsheet, and am unfamiliar with vba coding.

Kind regards,

Chris

 

[Macropod]

Instead of treating this forum as a free coding service - as evidenced by the numerous threads you've started seeking help with VBA - it's time you started learning. Having someone spoon-feed you with yet another solution isn't going to help with that. Solving your problem really isn't that difficult and a search here will soon turn up threads with coding examples using the ExportAsFixedFormat method.

 

[palaeontology]

Hi Macropod, I'm actually quite insulted by your terse reply.

If you look back through my history, in both this username, and the one I used many years ago (closer to 2002) you will find that I have answered dozens of people's formula-based queries. Some solutions so extensive, that they were emailed off-site due to their complexity and large file size.

I have learned a great deal from this site, regarding vba coding, but only to the point where I can read code, and understand much of what it's saying, but writing code is beyond me.

I have enrolled in several online vba coding courses over the past 2 years, but being able to compile complex formulae doesn't always translate into an ability to write code.

I had always believed this site was a give-and-take forum. I believe I have given a good deal to the users of this site over the years, and occasionally need assistance from them in return when it comes to coding.

I could understand your ire if I wasn't a contributor, but to write so harshly to me, as you did, is unwarranted.

I will attempt to break down 'ExportAsFixedFormat' method that you mentioned, but I fear it is far beyond my ability to read code.

 

[palaeontology]

I can no longer see my thread in the posts ... have you removed my thread from public view ?

Kind regards,

Chris

 

[Macropod]

No-one has done anything to your thread or posts.