"Insert Copied Cells" question - Excel 2010

[wilkisa]

My user has a data table that begins with a header row. Table range is presently A1:M44 with Row 1 being the header row. He wants to copy the entire header row by selecting the Row 1 selector, then INSERT COPIED CELLS every 5 rows down. Excel will allow him to copy/insert copied cells one time only. After that, he only has the option to INSERT which puts in a blank row. The copied row is still selected like it is on the clipboard. I also have XL07 as well as XL10 and can duplicate his problem.

I have tried several tricks to work around it but am unable to do so. Does anyone else have a suggestion, other than the obvious to tell the user to insert blank rows then paste the data? There doesn't seem to be a predefined tool in the All Commands list either. I can't decide if this is a bug or by design.

 

[Nalani]

If a macro is an option, try this placed in a standard module:

Sub InsertHeader()
    Dim LR As Long
        LR = Range("A" & Rows.Count).End(xlUp).Row / 5
    Application.ScreenUpdating = False
    Application.Goto Reference:=ActiveSheet.Range("A1")
    For i = 1 To LR
        With Rows("1:1").Copy
            ActiveCell.Offset(6, 0).Select
            Selection.Insert Shift:=xlDown
        End With
    Next i
    Application.CutCopyMode = False
End Sub

 

[wilkisa]

Thanks, Nalani, for the macro. I'm still curious about the problem, though. Is it truly by design or is it a bug? Anyone know the answer?

 

[Smitty]

See: Overview of Excel Tables And Add or remove table rows.

HTH,