Using a loop to set and copy a range

[jasonfoz]

Thanks Gibbo and Joseph;

I will give this a try a little later but in the meantime found a work around that seems to have done the job, although I think your coding looks a lot neater. If it does indeed work I may be replacing my code...

This is the code I eventually used and it seems to have worked... Thanks again for coming back to me.

With Worksheets("Paysheet") 
    ec = 0
    ' find the last column, and name each column on the way
    Do Until Cells(1, 1).Offset(0, ec).Text = ""
        lastColumn = ec
        Set rangeToName = .Range(Cells(1, 1).Offset(0, ec), Cells(2, 1).Offset(0, ec))
        rangeToName.CreateNames Top:=True
        ec = ec + 1
    Loop
    Dim RowCounts As Integer
    RowCounts = 1
    With Worksheets("Paysheet")
        While Cells(RowCounts, 1).Value <> ""
            RowCounts = RowCounts + 1
        Wend
        RowCounts = RowCounts - 1
    End With
    Range(Cells(1, 1), Cells(1, 1).Offset(RowCounts - 1, ec - 1)).Select
    ActiveSheet.Range(Cells(1, 1), Cells(1, 1).Offset(RowCounts - 1, ec - 1)).Name = "MyTable"