Macro for Archiving

[sprucethebruce]

I've got a group of cells I want to archive, the information is put in by someone else on a daily basis. I want the archiving to happen by filling up the top column then when the end is reached it automatically moves down 45 rows goes back to the start and repeats again.

I have no problem with the getting a macro to fill one row, its the moving down 45 rows once it reaches the end and starting again.

Anyone able to help me on this one?

Its A1:H45 on a worksheet named 'Copy', archiving to a worksheet in same workbook called 'archive'.

 

[sprucethebruce]

OK, I'm nearly there. I think. I've done some workarounds which have stopped the need for any tricky programming.

One last thing that seems to be failing me is, the recorded part of the macro where I'm copying the cells, it comes up with a bug everytime.

I'm trying to paste it so it is values only and maintains original source formatting.

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False