Any tricks for speeding up this working code?
 

Hi,
This code runs, but is kind of slow. I was wondering if instead of copying
the sheet, (formatting and all) it would be faster to do an Add sheet, copy
raw info in, then paste formatting and page setup AFTERWARD to all the
recently created sheets. The problem is, I don't know how to do it and I'm
not sure it would work anyhow. Also, if you can think of any other way to
speed things along, I'd appreciate it.
--
Thanks,
PTweety

Sub MakeStudentPages()

Dim wksScroll As Worksheet
Dim wksTemp As Worksheet
Dim wksNew As Worksheet
Dim nameLoop As Range
Dim currName As Range

Set wksScroll = Sheets("Scroll List")
Set wksTemp = Sheets("Student Profile Template")


'This code selects the student list on "scroll list" sheet
With wksScroll
Set nameLoop = .Range("a1", .Range("a1").End(xlDown))
End With

'Grab print range
Sheets("Student Profile Template").Activate
Application.Goto reference:="print_area"
Set r = Selection

'Loop through each name
For Each currName In nameLoop
With wksTemp
.Range("a3").Value = currName 'I tried replaced the A3 with a named
range but it didn't work. What am I missing? Tried both with and without
quotes.
.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
End With

'Create new sheet for student
wksTemp.Copy Befo=wksScroll
Set wksNew = ActiveSheet

With wksNew
'Make print range
ActiveSheet.PageSetup.PrintArea = r.Address
'Name new worksheet and calc it
.Name = Trim(currName, 31)
ActiveSheet.Calculate
'Replace formulas with values
.Cells.Copy
.Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End With

Next currName

'Hide working sheets
Sheets("Student Profile Template").Visible = False
Sheets("scroll list").Visible = False



End Sub

Any tricks for speeding up this working code?
 

Hi,

I haven't waded through your code but this will give an increase in speed

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

'your code

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Mike

"pickytweety" wrote:

Hi,
This code runs, but is kind of slow. I was wondering if instead of copying
the sheet, (formatting and all) it would be faster to do an Add sheet, copy
raw info in, then paste formatting and page setup AFTERWARD to all the
recently created sheets. The problem is, I don't know how to do it and I'm
not sure it would work anyhow. Also, if you can think of any other way to
speed things along, I'd appreciate it.
--
Thanks,
PTweety

Sub MakeStudentPages()

Dim wksScroll As Worksheet
Dim wksTemp As Worksheet
Dim wksNew As Worksheet
Dim nameLoop As Range
Dim currName As Range

Set wksScroll = Sheets("Scroll List")
Set wksTemp = Sheets("Student Profile Template")


'This code selects the student list on "scroll list" sheet
With wksScroll
Set nameLoop = .Range("a1", .Range("a1").End(xlDown))
End With

'Grab print range
Sheets("Student Profile Template").Activate
Application.Goto reference:="print_area"
Set r = Selection

'Loop through each name
For Each currName In nameLoop
With wksTemp
.Range("a3").Value = currName 'I tried replaced the A3 with a named
range but it didn't work. What am I missing? Tried both with and without
quotes.
.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
End With

'Create new sheet for student
wksTemp.Copy Befo=wksScroll
Set wksNew = ActiveSheet

With wksNew
'Make print range
ActiveSheet.PageSetup.PrintArea = r.Address
'Name new worksheet and calc it
.Name = Trim(currName, 31)
ActiveSheet.Calculate
'Replace formulas with values
.Cells.Copy
.Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End With

Next currName

'Hide working sheets
Sheets("Student Profile Template").Visible = False
Sheets("scroll list").Visible = False



End Sub

Any tricks for speeding up this working code?
 

I loooove you, maaaaaan! Thanks so much.
--
Thanks,
PTweety


"Mike H" wrote:

Hi,

I haven't waded through your code but this will give an increase in speed

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

'your code

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Mike

"pickytweety" wrote:

Hi,
This code runs, but is kind of slow. I was wondering if instead of copying
the sheet, (formatting and all) it would be faster to do an Add sheet, copy
raw info in, then paste formatting and page setup AFTERWARD to all the
recently created sheets. The problem is, I don't know how to do it and I'm
not sure it would work anyhow. Also, if you can think of any other way to
speed things along, I'd appreciate it.
--
Thanks,
PTweety

Sub MakeStudentPages()

Dim wksScroll As Worksheet
Dim wksTemp As Worksheet
Dim wksNew As Worksheet
Dim nameLoop As Range
Dim currName As Range

Set wksScroll = Sheets("Scroll List")
Set wksTemp = Sheets("Student Profile Template")


'This code selects the student list on "scroll list" sheet
With wksScroll
Set nameLoop = .Range("a1", .Range("a1").End(xlDown))
End With

'Grab print range
Sheets("Student Profile Template").Activate
Application.Goto reference:="print_area"
Set r = Selection

'Loop through each name
For Each currName In nameLoop
With wksTemp
.Range("a3").Value = currName 'I tried replaced the A3 with a named
range but it didn't work. What am I missing? Tried both with and without
quotes.
.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
End With

'Create new sheet for student
wksTemp.Copy Befo=wksScroll
Set wksNew = ActiveSheet

With wksNew
'Make print range
ActiveSheet.PageSetup.PrintArea = r.Address
'Name new worksheet and calc it
.Name = Trim(currName, 31)
ActiveSheet.Calculate
'Replace formulas with values
.Cells.Copy
.Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End With

Next currName

'Hide working sheets
Sheets("Student Profile Template").Visible = False
Sheets("scroll list").Visible = False



End Sub