Hi,
This code dies on line 63. It give me an error 424 and says I don't have
the required object. However, I copied this code from another file and used
almost the same syntax. Does anyone know how to put in the required object?


Sub runScores()


Dim wksSummary As Worksheet
Dim wksScroll As Worksheet
Dim perCell As Range
Dim perLoop As Range
Dim strCell As Range
Dim strLoop As Range
Dim wksTemplate As Worksheet

Set wksScroll = Sheets("scroll list")
Set wksTemplate = Sheets("Template")
Set wksSummary = Sheets("summary")


'clear the old "summary" page
With wksSummary
.Range("a7", .Range("a7").End(xlDown)).EntireRow.ClearContents
End With


'Select the list of periods (range) on "scroll list" sheet
With wksScroll
Set perLoop = .Range("b1", .Range("b1").End(xlDown))
End With

'Select the list of stores (range) on "scroll list" sheet
With wksScroll
Set strLoop = .Range("a1", .Range("a1").End(xlDown))
End With

'Loop through each period/str
For Each perCell In perLoop
With wksTemplate
.Range("g6").Value = perCell
End With
For Each strCell In strLoop
With wksTemplate
.Range("b1").Value = strCell
.Calculate
strLocation = .Range("B1").Value
End With
CopyToNext wksSummary 'fill in the next line of the "summary" sheet
Next strCell
Next perCell

wksSummary.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
Range("a1").Select

End Sub


Sub CopyToNext(wks As Worksheet)

Dim rngfill As Range

'MsgBox wks.Name

With wksSummary
.Outline.ShowLevels RowLevels:=2, ColumnLevels:=2 'THIS IS LINE 63
WHERE IT BOMBS
.Calculate
Set rngfill = Nothing
Set rngfill = .Range("A" & .Rows.Count).End(xlUp)
Set rngfill = rngfill.Offset(1, 0)

Rows("3:3").Copy
rngfill.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

rngfill.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False
End With

End Sub




--
Thanks,
PTweety

Replace 'With wksSummary'

with

'With wks'

--
If this post helps click Yes
---------------
Jacob Skaria


"pickytweety" wrote:


Hi,
This code dies on line 63. It give me an error 424 and says I don't have
the required object. However, I copied this code from another file and used
almost the same syntax. Does anyone know how to put in the required object?


Sub runScores()


Dim wksSummary As Worksheet
Dim wksScroll As Worksheet
Dim perCell As Range
Dim perLoop As Range
Dim strCell As Range
Dim strLoop As Range
Dim wksTemplate As Worksheet

Set wksScroll = Sheets("scroll list")
Set wksTemplate = Sheets("Template")
Set wksSummary = Sheets("summary")


'clear the old "summary" page
With wksSummary
.Range("a7", .Range("a7").End(xlDown)).EntireRow.ClearContents
End With


'Select the list of periods (range) on "scroll list" sheet
With wksScroll
Set perLoop = .Range("b1", .Range("b1").End(xlDown))
End With

'Select the list of stores (range) on "scroll list" sheet
With wksScroll
Set strLoop = .Range("a1", .Range("a1").End(xlDown))
End With

'Loop through each period/str
For Each perCell In perLoop
With wksTemplate
.Range("g6").Value = perCell
End With
For Each strCell In strLoop
With wksTemplate
.Range("b1").Value = strCell
.Calculate
strLocation = .Range("B1").Value
End With
CopyToNext wksSummary 'fill in the next line of the "summary" sheet
Next strCell
Next perCell

wksSummary.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
Range("a1").Select

End Sub


Sub CopyToNext(wks As Worksheet)

Dim rngfill As Range

'MsgBox wks.Name

With wksSummary
.Outline.ShowLevels RowLevels:=2, ColumnLevels:=2 'THIS IS LINE 63
WHERE IT BOMBS
.Calculate
Set rngfill = Nothing
Set rngfill = .Range("A" & .Rows.Count).End(xlUp)
Set rngfill = rngfill.Offset(1, 0)

Rows("3:3").Copy
rngfill.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

rngfill.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False
End With

End Sub




--
Thanks,
PTweety

from
Sub CopyToNext(wks As Worksheet)

to
Sub CopyToNext(wksSummary As Worksheet)


"pickytweety" wrote:


Hi,
This code dies on line 63. It give me an error 424 and says I don't have
the required object. However, I copied this code from another file and used
almost the same syntax. Does anyone know how to put in the required object?


Sub runScores()


Dim wksSummary As Worksheet
Dim wksScroll As Worksheet
Dim perCell As Range
Dim perLoop As Range
Dim strCell As Range
Dim strLoop As Range
Dim wksTemplate As Worksheet

Set wksScroll = Sheets("scroll list")
Set wksTemplate = Sheets("Template")
Set wksSummary = Sheets("summary")


'clear the old "summary" page
With wksSummary
.Range("a7", .Range("a7").End(xlDown)).EntireRow.ClearContents
End With


'Select the list of periods (range) on "scroll list" sheet
With wksScroll
Set perLoop = .Range("b1", .Range("b1").End(xlDown))
End With

'Select the list of stores (range) on "scroll list" sheet
With wksScroll
Set strLoop = .Range("a1", .Range("a1").End(xlDown))
End With

'Loop through each period/str
For Each perCell In perLoop
With wksTemplate
.Range("g6").Value = perCell
End With
For Each strCell In strLoop
With wksTemplate
.Range("b1").Value = strCell
.Calculate
strLocation = .Range("B1").Value
End With
CopyToNext wksSummary 'fill in the next line of the "summary" sheet
Next strCell
Next perCell

wksSummary.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
Range("a1").Select

End Sub


Sub CopyToNext(wks As Worksheet)

Dim rngfill As Range

'MsgBox wks.Name

With wksSummary
.Outline.ShowLevels RowLevels:=2, ColumnLevels:=2 'THIS IS LINE 63
WHERE IT BOMBS
.Calculate
Set rngfill = Nothing
Set rngfill = .Range("A" & .Rows.Count).End(xlUp)
Set rngfill = rngfill.Offset(1, 0)

Rows("3:3").Copy
rngfill.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

rngfill.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False
End With

End Sub




--
Thanks,
PTweety