Macro to copy down columns of data

Hi,

I have a work sheet with data in the first 6 columns and I believe the data has a one to many relationship, in that the first four columns i.e. ‘A’ to ‘D’ are only populated once with the remaining two fields ‘E’ and ‘F’ populated down with the Work Order & Comments data until the next record is found.

What I would like is the macro code to copy and populate the data in the first four columns down ‘A’ to ‘D’ to the next row with data in i.e. in columns ‘A’ to ‘F’.

Any assistance in this matter would be greatly appreciated.

Many thanks in advance.


Rob

N.B. excel version 2010

Try:

Sub CopyDown()

Dim LR As Long

LR = Range("E" & ActiveSheet.UsedRange.Rows.Count + 2).End(xlUp).Row
With Range("A2:D" & LR)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Copy
    .PasteSpecial (xlPasteValues)
End With
End Sub

Obsessed,

many thanks for you reply it is very much appreciated. I ran the macro and it worked as required...however, when I ran it again to see what would happen I got a 'Run-time error '1004':'. Do you know how to amend the code to check if no data needs to be processed and thus avoid a 'Run-time error'

Many thanks

Rob

Here you go:

Sub CopyDown()

Dim LR As Long, c As Long

LR = Range("E" & ActiveSheet.UsedRange.Rows.Count + 2).End(xlUp).Row

With Range("A2:D" & LR)
    On Error Resume Next
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Copy
    .PasteSpecial (xlPasteValues)
End With
End Sub

Obsessed,

EXCELLENT !!

Many thanks indeed

Regards

Rob

No Problem, thanks for the rep!