Hi,
This is code that Matt Herbert so kindly wrote for me. On line 111, near
the bottom, I think it's not referring to the right worksheet in the
workbook. I think it's stuck on the new sheet that was created as opposed to
wksDirBonus. Why isn't the "CopyToNext wksDirBonus" pointing it to the
correct place for pasting row 5? (In other words, row 5 in the wksDirBonus
should get pasted as values into the same worksheet, just further down, with
each item in the rngLoop.)
--
Thanks,
PTweety
PS Matt, if you happen to see this, I really appreciate you taking the time
to write this. I have so many files now that I need to go back and clean up.
Your advice will make my work much more efficient. Thank you. Is rating
the post just clicking "Yes" or "No"? I never did get to a place that let me
rate your awesome response. Thanks to Bernie Deitrick too.
Sub RunReport()
Dim strLocation As String
Dim rngLoop As Range
Dim rngCell As Range
Dim wksTemp As Worksheet
Dim wksScroll As Worksheet
Dim wksNew As Worksheet
Dim wksDirBonus As Worksheet
Dim wksAstBonus As Worksheet
Dim rngfill As Range
'set the Template and Scroll List worksheets as objects
Set wksTemp = Sheets("Template")
Set wksScroll = Sheets("scroll list")
Set wksDirBonus = Sheets("YTD dir bonus summary")
Set wksAstBonus = Sheets("YTD asst bonus summary")
'clear the old "YTD dir bonus summary" page
With wksDirBonus
.Range("a9", .Range("a9").End(xlDown)).EntireRow.ClearContents
End With
'clear the old "YTD asst bonus summary" page
With wksAstBonus
.Range("a9", Range("a9").End(xlDown)).EntireRow.ClearContents
End With
'Select the list of stores (range) on "scroll list" sheet
With wksScroll
Set rngLoop = Range("a1", .Range("a1").End(xlDown))
End With
'show outline levels on wksTemp
wksTemp.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
'Loop through each cell in rngLoop
For Each rngCell In rngLoop
With wksTemp
.Range("B1").Value = rngCell
.Calculate
strLocation = .Range("B1").Value
End With
'Create new sheet for strLocation and name it
wksTemp.Copy Before:=wksTemp
Set wksNew = ActiveSheet
With wksNew
.Name = Trim(strLocation)
'Select cells and replace formulas with values
.Cells.Copy
.Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End With
'fill in the next line of wksDirBonus
CopyToNext wksDirBonus
'fill in the next line of wksAstBonus
CopyToNext wksAstBonus
Next
wksDirBonus.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
wksAstBonus.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
'Hide working sheets
Sheets("Template").Visible = False
Sheets("Instructions").Visible = False
Sheets("str list").Visible = False
Sheets("SOSP03").Visible = False
Sheets("SOSP03 YTD").Visible = False
Sheets("ident sales").Visible = False
Sheets("ident sales YTD").Visible = False
Sheets("not ident history").Visible = False
Sheets("SOSP04-Inv").Visible = False
Sheets("SOSP05-labor actuals").Visible = False
Sheets("SOSP05 YTD-labor actuals").Visible = False
Sheets("Gordy's labor bud").Visible = False
Sheets("Gordy's labor bud YTD").Visible = False
Sheets("Poulsen's P&G focus QTR").Visible = False
Sheets("Gary's bonus").Visible = False
Sheets("Hal's out of stock").Visible = False
Sheets("Cust 1st fr Mys Shop").Visible = False
Sheets("Sales Brackets").Visible = False
Sheets("Mys Shop Goals").Visible = False
Sheets("Key Retailing").Visible = False
Sheets("Rod's Turnover").Visible = False
Sheets("Mark's Safety").Visible = False
Sheets("Bill's Loyalty").Visible = False
Sheets("Points Summary").Visible = False
Sheets("scroll list").Visible = False
End Sub
Sub CopyToNext(wks As Worksheet)
Dim rngfill As Range
With wks
.Calculate
Set rngfill = Nothing
Set rngfill = Range("A" & Rows.Count).End(xlUp)
Set rngfill = rngfill.Offset(1, 0)
Rows("5:5").Copy
Line111 rngfill.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
