P
pickytweety
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 Before:=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
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 Before:=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