Looping within a loop
 

Hi,
I want to run each store, within each period and put a line in a summary
sheet with each store/period, but I've got it wrong in the code. Can someone
help?
--
Thanks,
PTweety
Sub runScores()

' RunScores Macro

Dim wksSummary As Worksheet
Dim wksScroll As Worksheet
Dim perCell As Range
Dim perLoop As Range
Dim strCell As RecentFile
Dim strLoop As Range
Dim wksTemplate As Worksheet
Dim strLocation As String

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

'Loop through each period
For Each perCell In perLoop
With wksTemplate
.Range("g6").Value = perCell
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 location within each period
For Each strCell In strLoop
With wksTemplate
.Range("b1").Value = strCell
.Calculate
strLocation = .Range("B1").Value
End With

'fill in the next line of the "summary" sheet
CopyToNext wksSummary

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 wks
..Outline.ShowLevels RowLevels:=2, ColumnLevels:=2
..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

Looping within a loop
 

I think the statement below is you problem. No worksheet is specified

Rows("3:3").Copy


"pickytweety" wrote:

Hi,
I want to run each store, within each period and put a line in a summary
sheet with each store/period, but I've got it wrong in the code. Can someone
help?
--
Thanks,
PTweety
Sub runScores()

' RunScores Macro

Dim wksSummary As Worksheet
Dim wksScroll As Worksheet
Dim perCell As Range
Dim perLoop As Range
Dim strCell As RecentFile
Dim strLoop As Range
Dim wksTemplate As Worksheet
Dim strLocation As String

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

'Loop through each period
For Each perCell In perLoop
With wksTemplate
.Range("g6").Value = perCell
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 location within each period
For Each strCell In strLoop
With wksTemplate
.Range("b1").Value = strCell
.Calculate
strLocation = .Range("B1").Value
End With

'fill in the next line of the "summary" sheet
CopyToNext wksSummary

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 wks
.Outline.ShowLevels RowLevels:=2, ColumnLevels:=2
.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