Run time Error 9 Help!!

[LikeButtah1]

I'm trying to distribute cells from a payroll sheet to individual employee sheets. It should find the last name on the payroll sheet in column B and distribute the pay or PTO used into the corresponding individual employee sheet but I keep getting Run Time Error 9 Subscript out of Range at the first Set ws = Sheets(LastNames(R, 1)) . The code is activated with a command button on the actual payroll sheet. I cannot see what is causing the error. Can someone take a look and let me know what I'm doing wrong. Thanks in advance.

Sub DistributeFromBiWeeklyPayroll()

  Dim R As Long
  Dim LastRow As Long
  Dim NextRow As Long
  Dim ws As Worksheet
  Dim LastNames As Variant
  Dim QData As Variant
  
  Application.ScreenUpdating = False
  
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  LastNames = Range("B4", Cells(LastRow, "B"))
  QData = Range("F4", Cells(LastRow, "F"))
  For R = 1 To UBound(LastNames)
    Set ws = Sheets(LastNames(R, 1))
    NextRow = Application.Max(5, ws.Cells(ws.Rows.Count, "C").End(xlUp).Row) + 1
    ws.Cells(NextRow, "C") = QData(R, 1)
  Next
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  LastNames = Range("B4", Cells(LastRow, "B"))
  QData = Range("N4", Cells(LastRow, "N"))
  For R = 1 To UBound(LastNames)
    Set ws = Sheets(LastNames(R, 1))
    NextRow = Application.Max(5, ws.Cells(ws.Rows.Count, "E").End(xlUp).Row) + 1
    ws.Cells(NextRow, "E") = QData(R, 1)
  Next

 

[Fluff]

That sounds as though one of the sheet names is wrong.

 

[steve the fish]

Firstly qualify your ranges with a sheet name. That macro will have different action depending on active sheet when run. Then either a cell is blank or has a sheet that doesnt exist most likely.

 

[LikeButtah1]

Is there a way to make this code skip a name that it cannot find? and go onto the next name? Thanks again in advance.

 

[Fluff]

How about

  For R = 1 To UBound(LastNames)
    If Evaluate("isref('" & LastNames & "'!A1)") Then
      Set ws = Sheets(LastNames(R, 1))
      NextRow = Application.Max(5, ws.Cells(ws.Rows.Count, "C").End(xlUp).Row) + 1
      ws.Cells(NextRow, "C") = QData(R, 1)
   End If
  Next

 

[steve the fish]

Why not remove it from your sheet? Im all for preventing errors but some errors you want to see. This looks like one of them.

 

[LikeButtah1]

I agree Steve but this workbook will have name changes throughout the year with people being added (not a problem) but people also being removed since they quit or are terminated. What this creates is a constant updating of the workbook that I am trying to avoid.

Fluff I'm getting error 13 from your code but thanks.

 

[Fluff]

Oops, it should be

  For R = 1 To UBound(LastNames)
    If Evaluate("isref('" & LastNames(R, 1) & "'!A1)") Then
      Set ws = Sheets(LastNames(R, 1))
      NextRow = Application.Max(5, ws.Cells(ws.Rows.Count, "C").End(xlUp).Row) + 1
      ws.Cells(NextRow, "C") = QData(R, 1)
   End If
  Next

 

[LikeButtah1]

I think the easiest route is simply to make an employee sheet and then maybe hide the sheet? The code will run no problem then.
I was hoping for the code the skip the fact that it cannot find the employee sheet name and move on the the next last name in column B but this way works too.

Fluff- still get the error 13 but I really appreciate your help. Thanks.

 

[Fluff]

Do you have any blank cells in col B of the active sheet