P
pickytweety
About half way down, where I type "LOOK HERE" I want to replace the "A3" with
a named range. I tried just replacing the A3 with a name and it didn't work.
So then I took the quotes off and it still didn't work. Can you help?
--
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")
'Turn Automatic Calculation off and screen updating off
'With my test run, turning off the screen update made it run 200% faster.
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
'This code selects the name range 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 'LOOK
HERE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
.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)
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("Letter-Sound Record").Visible = False
Sheets("enter data here").Visible = False
Sheets("scroll list").Visible = False
Sheets("Questions for Candi").Visible = False
'Turn Automatic Calculation back on and screen updating back on
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
a named range. I tried just replacing the A3 with a name and it didn't work.
So then I took the quotes off and it still didn't work. Can you help?
--
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")
'Turn Automatic Calculation off and screen updating off
'With my test run, turning off the screen update made it run 200% faster.
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
'This code selects the name range 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 'LOOK
HERE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
.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)
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("Letter-Sound Record").Visible = False
Sheets("enter data here").Visible = False
Sheets("scroll list").Visible = False
Sheets("Questions for Candi").Visible = False
'Turn Automatic Calculation back on and screen updating back on
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub