Re: Runtime error 9 - subscript out of range error

[VbaHell]

Re: Runtime error 9 - subscript out of range error

Hello All

I hope you can help on this please

Re: Runtime error 9 - subscript out of range error on the code below that is bold​

[Lastrow = Sheets("data").Cells(Rows.Count, "I").End(xlUp).Row
For i = 2 To Lastrow

If Cells(i, "J").Value = "D" Then Cells(i, 1).Resize(, 8).Copy Sheets(Cells(i, "I").Value).Cells(Sheets(Cells(i, "I").Value).Cells(Rows.Count, "K").End(xlUp).Row + 1, "K")

If Cells(i, "J").Value = "C" Then Cells(i, 1).Resize(, 8).Copy Sheets(Cells(i, "I").Value).Cells(Sheets(Cells(i, "I").Value).Cells(Rows.Count, "B").End(xlUp).Row + 1, "B")

Next]

 

[Fluff]

Re: Runtime error 9 - subscript out of range error

Do you have a sheet name with whatever is in cells(i,"I") when you get the error?

 

[VbaHell]

Re: Runtime error 9 - subscript out of range error

Hi Fluff

This is my full code

Sub Populate()
Application.ScreenUpdating = False
'On Error GoTo M
Dim i As Long
Dim ans As Long
Sheets(Array("August 18", "July 18", "June 18", "May 18", "April 18", "March 18", "February 18", "January 18", "December 17", _
        "November 17", "October 17", "September 17", "August 17", "July 17")).Select
    Sheets("August 18").Activate
    Range("B9:I80").Select
    Selection.ClearContents
    
    Range("K9:R80").Select
    Selection.ClearContents
    
Sheets("data").Activate
Dim Lastrow As Long
Lastrow = Sheets("data").Cells(Rows.Count, "B").End(xlUp).Row
Range("K1").FormulaR1C1 = "Text Date"
    With Range("K2:K" & Lastrow)
        .FormulaR1C1 = "=TEXT(RC[-2],""mmmm yy"")"
        '.Value = .Value
    End With
    
 Columns("K:K").Select
    Selection.Copy
    Columns("I:I").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Lastrow = Sheets("data").Cells(Rows.Count, "B").End(xlUp).Row
    For i = 2 To Lastrow
        If Cells(i, "J").Value = "D" Then Cells(i, 1).Resize(, 8).Copy Sheets(Cells(i, "I").Value).Cells(Sheets(Cells(i, "I").Value).Cells(Rows.Count, "K").End(xlUp).Row + 1, "K")
        If Cells(i, "J").Value = "C" Then Cells(i, 1).Resize(, 8).Copy Sheets(Cells(i, "I").Value).Cells(Sheets(Cells(i, "I").Value).Cells(Rows.Count, "B").End(xlUp).Row + 1, "B")
     
     Next
     
 Call Formats
    
Application.ScreenUpdating = True
Exit Sub
'M:
'MsgBox "That sheet name does not exist or you had some other sort of problem"
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Re: Runtime error 9 - subscript out of range error

When posting code please use code tags, the # icon in the reply window.
Also you haven't answered my question

 

[VbaHell]

Re: Runtime error 9 - subscript out of range error

Sorry for not posting the code correctly, I will insert the icon next time

"

Do you have a sheet name with whatever is in cells(i,"I") when you get the error? " - With the error occurring when I hover over (i'"I") I get nothing at all, if this is what your asking

I do get I = 222 which is the last row, That's the (i, (Little i)
​

 

[Fluff]

Re: Runtime error 9 - subscript out of range error

If cells(i,"I") is empty that would explain the error as you cannot have a sheet without a name

 

[VbaHell]

Re: Runtime error 9 - subscript out of range error

Can you point me in the right direction
How do I can or add to the code, it populate's the Sheets(array (All the tabs with the months)

Should the array be referenced to "I" some how

 

[Fluff]

Re: Runtime error 9 - subscript out of range error

What do you want to happen if the sheet does not exist?

 

[VbaHell]

Re: Runtime error 9 - subscript out of range error

It copies from the sheet named "Data" and inputs into all the tabs in the sheet array named below

[LEFT][COLOR=#333333][FONT=monospace]Sheets(Array("August 18", "July 18", "June 18", "May 18", "April 18", "March 18", "February 18", "January 18", "December 17", _
        "November 17", "October 17", "September 17", "August 17", "July 17")).Select
    Sheets("August 18").Activate[/FONT][/COLOR][/LEFT]

If the value in the Data sheet column "J" ="D" then copy the row to all the tabs in the array from column "K"

If the value in the Data sheet column "J" ="C" then copy the row to all the tabs in the array from column "B"​

 

[Fluff]

Re: Runtime error 9 - subscript out of range error

You're code does not copy the data to all the tabs. It copies the data to a sheet with the same name as the cells in col I.