Referencing/selecting

[daveyboy23]

Hi guys
I have a vba code set up but am not able to select a different sheet in the unsaved workbook. I get "subscript out of range error"
The code is
stuff

ND

. Press the Enter key to open the contact card." style="unicode-bidi: embed; direction: ltr; text-align: left; user-select: text; cursor: pointer;">Newbury, David <david.newbury@northernpowergrid.com>

Wed 19/12/2018 17:03

Inbox

To:

somebody123@outlook.com;

Sub trying()

 

Dim filter_rng As Range

Dim rw, rng As Range

Dim lastrow As Long

Dim strSupp As String

Dim MainWkbk As Workbook

Dim NextWkbk As Workbook

Dim strInvPath

 

 

strInvPath = Sheets("Header").Range("A2").Value

If Right(strInvPath, 1) <> "" Then strInvPath = strInvPath & ""

 

'create Backing Data

Sheets("Summary").Select

lastrow = Range("A" & Rows.Count).End(xlUp).Row

 

Set filter_rng = Sheets("Summary").Range("A2:A" & lastrow)

 

For Each rw In filter_rng.SpecialCells(xlCellTypeVisible)

 

strSupp = rw

 

Workbooks.Add

Set NextWkbk = ActiveWorkbook

Application.Calculation = xlCalculationManual

Application.DisplayAlerts = False

 

Workbooks("Churn Compliance Status as at 05Dec18").Activate

Workbooks("Churn Compliance Status as at 05Dec18").Worksheets("Feature").Select

If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData

lastrow = Range("A" & Rows.Count).End(xlUp).Row

 

Set r = Range("A1:J" & lastrow)

r.AutoFilter Field:=4, Criteria1:=strSupp

lastrow = Range("A" & Rows.Count).End(xlUp).Row

Range("A1:S" & lastrow).Copy

 

NextWkbk.Activate

NextWkbk.Sheets("Sheet1").Range("A1").Select

ActiveSheet.Paste

Sheets.Add

ActiveWorkbook.Sheets("Sheet1").Name = "EDMI"

ActiveWorkbook.Sheets("Sheet4").Name = "Summary"

ActiveWorkbook.Sheets("Sheet2").Name = "Itron"

ActiveWorkbook.Sheets("Sheet3").Name = "Aclara"

 

Workbooks("Churn Compliance Status as at 05Dec18").Activate

Workbooks("Churn Compliance Status as at 05Dec18").Worksheets("Second").Select

If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData

lastrow = Range("A" & Rows.Count).End(xlUp).Row

 

Set r = Range("A1:J" & lastrow)

r.AutoFilter Field:=4, Criteria1:=strSupp

lastrow = Range("A" & Rows.Count).End(xlUp).Row

Range("A1:S" & lastrow).Copy

 

NextWkbk.Activate

NextWkbk.Sheets("Sheet2").Range("A1").Select

ActiveSheet.Paste

 

 

NextWkbk.Activate

NextWkbk.SaveAs strInvPath & strSupp & ".xlsx"

NextWkbk.Close

   

Next rw

 

End Sub

</david.newbury@northernpowergrid.com>

 

[Special-K99]

"Subscript out of range" can also be generated when a sheet referred to does not exist.