Unexpected error when ActiveWorksheet not source w

Question

I have a worksheet with column headers (in row 1) and under each header an array of between 1 and 255 values. I have code that creates a new sheet for each heading and paste the values (starting from row 2 to row n, different for each header) and renames the sheet to the heading title. Assume that the heading titles are valid sheet names.

The code below works fine when my ActiveSheet is the Data worksheet.

However, when I run the code with a different ActiveSheet (within the same workbook) I get this error:

Run-time error 1004: Method Range’ of object ‘_Worksheet failed

The debugger tells me that the error occurs on the line Set src = ws.Range(Cells(2, i), Cells(lastRow, i)). Why is this error occurring?

Sub MakeNewWorksheets()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim target As Worksheet
    Dim i As Long
    Dim s As String

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Data")

    For i = 1 To ws.UsedRange.Columns.Count
        s = ws.Cells(1, i)
        If Not SheetExists(s, wb) Then
            Set target = wb.Sheets.Add(, wb.Worksheets(wb.Worksheets.Count))
            target.Name = s
        Else
            Set target = wb.Worksheets(s)
        End If

        ' Find data from front sheet
        Dim src As Range
        Dim lastRow As Long
        lastRow = Cells(Rows.Count, i).End(xlUp).Row

        Set src = ws.Range(Cells(2, i), Cells(lastRow, i))
        'Debug.Print src.Address

        ' Set values in target sheet
        target.Range("A1:A256").Value = src.Value
    Next i

End Sub

Answer 1

This error is occuring because the Cells(2,1) and the Cells(lastRow, i) are not specifically linked to a sheet in your code and thus linked to the ActiveSheet. The command you give should create a range on sheet ws with cells on sheet ActiveSheet. This is not possible.

Change it into (add ws. in front of the Cells(..):

Set src = ws.Range(ws.Cells(2, i), ws.Cells(lastRow, i))