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 Befo=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

Your objective is not clear. The loop, as written, will end up with the
value of the last cell with data in column A of wksScroll being entered in
cell A3 of wksTemp. So, starting from there, exactly what did you expect to
see in Range("a3") of wksTemp? Define what you mean by:
"I want to replace the "A3" with a named range." Do you mean that you want
to Set A3 to an object variable, or do you mean you want to actulally name
cell A3? You can name the cell by clicking InsertNameDefine, etc. You
need to clarify exactly what your objective is for the macro.


"pickytweety" wrote in message
...
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 Befo=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

A3 hold the name of the current student. The macro goes through a list of
students and creates a sheet for them summarizing their scores, then moves to
the next student. Someone inserted a row in the template sheet, causing
trouble. If I had used a named range instead of A3 in the macro this
wouldn't have been a problem. I've given A3 a name--currStudent. Now how do
I use currStudent in the VBA code rather than A3. Does that make sense?
--
Thanks,
PTweety


"JLGWhiz" wrote:

Your objective is not clear. The loop, as written, will end up with the
value of the last cell with data in column A of wksScroll being entered in
cell A3 of wksTemp. So, starting from there, exactly what did you expect to
see in Range("a3") of wksTemp? Define what you mean by:
"I want to replace the "A3" with a named range." Do you mean that you want
to Set A3 to an object variable, or do you mean you want to actulally name
cell A3? You can name the cell by clicking InsertNameDefine, etc. You
need to clarify exactly what your objective is for the macro.


"pickytweety" wrote in message
...
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 Befo=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

If you have named a range currStudent, then in the code you would use:

Range("currStudent")


"pickytweety" wrote in message
...
A3 hold the name of the current student. The macro goes through a list of
students and creates a sheet for them summarizing their scores, then moves
to
the next student. Someone inserted a row in the template sheet, causing
trouble. If I had used a named range instead of A3 in the macro this
wouldn't have been a problem. I've given A3 a name--currStudent. Now how
do
I use currStudent in the VBA code rather than A3. Does that make sense?
--
Thanks,
PTweety


"JLGWhiz" wrote:

Your objective is not clear. The loop, as written, will end up with the
value of the last cell with data in column A of wksScroll being entered
in
cell A3 of wksTemp. So, starting from there, exactly what did you expect
to
see in Range("a3") of wksTemp? Define what you mean by:
"I want to replace the "A3" with a named range." Do you mean that you
want
to Set A3 to an object variable, or do you mean you want to actulally
name
cell A3? You can name the cell by clicking InsertNameDefine, etc.
You
need to clarify exactly what your objective is for the macro.


"pickytweety" wrote in message
...
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 Befo=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