Help copying info down to last line on sheet.

[Kirnon]

Using a solution for another column I tried to fill in some detail for a sheet that gets thrown into excel. The code is as follows.

Sub Test()
    Columns("H:I").Insert Shift:=xlToLeft
    With Range("A1").CurrentRegion.Columns(1)
        .Offset(RowCount, 7).Value = "Pay"
        .Offset(RowCount, 8).Value = "Basic"
        .Offset(RowCount + 1, 7).Value = "Pay"
        .Offset(RowCount + 1, 8).Value = "Day Rate"
        .Offset(RowCount + 2, 7).Value = "Overtime"
        .Offset(RowCount + 2, 8).Value = "O/T 1.5"
        .Offset(RowCount + 3, 7).Value = "Holiday"
        .Offset(RowCount + 3, 8).Value = "Hol Pay"
        .Offset(RowCount + 4, 7).Value = "Holiday"
        .Offset(RowCount + 4, 8).Value = "Bnk Hol Pay"
        .Offset(RowCount + 5, 7).Value = "RD Work"
        .Offset(RowCount + 5, 8).Value = "Mon-Fri"
        .Offset(RowCount + 6, 7).Value = "RD Work"
        .Offset(RowCount + 6, 8).Value = "Sat"
        .Offset(RowCount + 7, 7).Value = "RD Work"
        .Offset(RowCount + 7, 8).Value = "Sun"
        .Offset(RowCount + 8, 7).Value = "Crooklands"
        .Offset(RowCount + 8, 8).Value = "RD Prem"
        .Offset(RowCount + 9, 7).Value = "Crooklands"
        .Offset(RowCount + 9, 8).Value = "Night Prem"
        .Offset(RowCount + 10, 7).Value = "Other"
        .Offset(RowCount + 10, 8).Value = "Bulk"
        .Offset(RowCount + 11, 7).Value = "Sick"
        .Offset(RowCount + 11, 8).Value = "Comp Sick"
        .Offset(RowCount + 12, 7).Value = "Sick"
        .Offset(RowCount + 12, 8).Value = "SSP"
        .Offset(RowCount + 13, 7).Value = "Sick"
        .Offset(RowCount + 13, 8).Value = "SPP"
    End With
End Sub

The problem with this particular code is that it continues down my data and only copies the last line ie. "SPP" instead of looping through like I thought it would.

I picked the "With Range("A1").CurrentRegion.Columns(1)" from a solution provided by Fazza to another user.

As I said it works for my other info which is simply copying a formula down but not here.

Can anybody help me?

 

[Peter_SSs]

Kirnon

Could you explain in words just what you want the code to do?

 

[Kirnon]

Hi,

I have an exported Pay information sheet which comes out without headings or labels of any kind and with terrible formatting.

I have worked out a bunch of code transposing numbers etc. to get it formatted for placing into a Pivot (Much of that code gleaned from these very boards). I am now attempting to put labels onto the information the code that I have included in my first post puts those labels into the correct columns and rows for the first individual.

The idea being that the next row down from the last offset should loop the code so that it once more starts with the first offset. this needs to continue till the last row of information. Currently - After completing all the Offset once correctly the code then continues on repeating the last row till the end of my data.

I hope that is clear.

Thanks for the quick reply.