How to index every line

[JBW]

I have the following code which uses myval to calculate correct number of
times to carry out function on sheet.

ActiveSheet.Name = "BB SCM " & FormatDateTime(Date, vbLongDate)



myVal = Application.CountIf(ActiveSheet.Cells, "NPQ") - 1


Range("Q6").Select
ActiveCell.FormulaR1C1 = _
"=AVERAGE(IF(RC[-13]:RC[-2]>0,RC[-13]:RC[-2],RC[-13]))"
Range("Q6").Select
Selection.FormulaArray = _
"=AVERAGE(IF(RC[-13]:RC[-2]>0,RC[-13]:RC[-2],RC[-13]))"


Dim counter As Integer

For counter = 1 To myVal


Cells(6 + (counter * 8), 17).Select
ActiveCell.FormulaR1C1 = _
"=AVERAGE(IF(RC[-13]:RC[-2]>0,RC[-13]:RC[-2],RC[-13]))"
Cells(6 + (counter * 8), 17).Select
Selection.FormulaArray = _
"=AVERAGE(IF(RC[-13]:RC[-2]>0,RC[-13]:RC[-2],RC[-13]))"



Range("A6").Select
ActiveCell.FormulaR1C1 = "Stk Wk"
Range("B6").Select
ActiveCell.FormulaR1C1 = "=IF(RC[15]>0,R[-1]C[2]/RC[15],""NF"")"
Range("B6").Select
Selection.NumberFormat = "0.0"


Cells(6 + (counter * 8), 1).Select
ActiveCell.FormulaR1C1 = "Stk Wk"
Cells(6 + (counter * 8), 2).Select
ActiveCell.FormulaR1C1 = "=IF(RC[15]>0,R[-1]C[2]/RC[15],""NF"")"
Cells(6 + (counter * 8), 2).Select
Selection.NumberFormat = "0.0"

Range("Q1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Fc ave"



Next counter



Sheets.Add
Sheets("Sheet1").Select
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets("Sheet5").Select
Sheets("Sheet5").Name = "FC v Actual"
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "Print Plan"
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "No Forecast"
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Code Missing"
Sheets("Sheet6").Select
Sheets("Sheet6").Name = "Refers"
Sheets("Sheet7").Select
Sheets("Sheet7").Name = "Stock by code"






End Sub

I need to use same method to index across cell data to another sheet and be
sure using myvl that I take every line reardless of how mnay are in report
(it varies week to week).

I have two problems.

1. How do I select the sheet named BB SCM + date without using its 'name' as
this will change every day.

2 and how can I get it to use =index(sheet reference! A:A, (row()-1)*8+2) on
the 'print plan' sheet for the myval number of times.

 

[Gary''s Student]

To Select a sheet whose name changes frequently, just insure the sheet has an
unchanged position w.r.t the other sheets and:

Sub ordinate()
Sheets(1).Select
End Sub