palaeontology
Active Member
- Joined
- May 12, 2017
- Messages
- 444
- Office Version
- 2016
- Platform
- Windows
I have the following code that does several things.
within that code, there is a code to create a copy of a template worksheet for each name in a list and name each of those new worksheets a particular cell value which happens to be a 4-digit code.
Then there's another code to print all sheets with a 4-digit number.
However, when the new sheets are made, it takes a little while for all the formulae on the new worksheet to retrieve the information they're designed to display.
Consequently, when the print code runs, some of the cells haven't finished updating, so the print isn't showing true data.
Is there a way to slow down how long it takes to print, after the sheets have been made ... perhaps 30 seconds should do it, just to be sure.
Very kind regards,
Chris
However,
Code:
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
ws.PageSetup.Orientation = xlLandscape
ws.PrintOut From:=2, To:=2
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 Subwithin that code, there is a code to create a copy of a template worksheet for each name in a list and name each of those new worksheets a particular cell value which happens to be a 4-digit code.
Then there's another code to print all sheets with a 4-digit number.
However, when the new sheets are made, it takes a little while for all the formulae on the new worksheet to retrieve the information they're designed to display.
Consequently, when the print code runs, some of the cells haven't finished updating, so the print isn't showing true data.
Is there a way to slow down how long it takes to print, after the sheets have been made ... perhaps 30 seconds should do it, just to be sure.
Very kind regards,
Chris
However,
