Insert line, sequential/auotfill number and keep formatting

[wilson187]

Hi everyone,

I am looking to produce a macro that will enable me to do the following:

Copy a selection of cells, for instance [A6:D6]
Insert the same row; in terms of formatting and row height into [A7:D7]

However, cell A6, contains the following data "a.1" and when the macro is run, cell A7 would be automatically populated with "a.2", so on and so forth.... cell A8 would be populated with "a.3"

Any help or suggestions would be greatly appreciated!

Many thanks
-Gary

 

[JoeMo]

You want to copy values and formatting from A6:D6 to how many rows while incrementing the col A entry's suffix number by 1 in each row?

 

[wilson187]

Hi Joe,

I would like to copy just one row at a time. So after copying a6:d6 to a7:d7, the macro would then copy a7:d7 to a8:d8 so on and so forth so a7.d7 would auto populate to "a.2" and a8:d8 to "a.3". I have a picture/snapshot to show but am unsure how to upload it to show you?

hope this helps.

-Gary

 

[JoeMo]

Hi Joe,

I would like to copy just one row at a time. So after copying a6:d6 to a7:d7, the macro would then copy a7:d7 to a8:d8 so on and so forth so a7.d7 would auto populate to "a.2" and a8:d8 to "a.3". I have a picture/snapshot to show but am unsure how to upload it to show you?

hope this helps.

-Gary

Your first sentence says you want to copy one row to the row below it. But, you then say you want that to continue ... so on and so forth. Do you want to simply run the macro again for the continuation or do you want the macro to continue on after the first row is copied? If the latter, then you need to tell us where - which row - you want the macro to stop copying.

 

[wilson187]

Your first sentence says you want to copy one row to the row below it. But, you then say you want that to continue ... so on and so forth. Do you want to simply run the macro again for the continuation or do you want the macro to continue on after the first row is copied? If the latter, then you need to tell us where - which row - you want the macro to stop copying.

To run the macro again for continuation.

Thanks

 

[JoeMo]

To run the macro again for continuation.

Thanks

See if this works for you.

Sub InsertCopiedRow()
Const Cols As Long = 4 '<-- set the number of columns you want to copy here. 4 would be A:D
Dim lR As Long
lR = Range("A" & Rows.Count).End(xlUp).Row
With Range("A" & lR).Resize(1, Cols)
    .Offset(1, 0).EntireRow.Insert
    .Copy Destination:=.Offset(1, 0)
    .Cells(1, 1).AutoFill Destination:=.Cells(1, 1).Resize(2, 1), Type:=xlFillDefault
End With
End Sub

 

[wilson187]

Perfect, thanks alot!

 

[wilson187]

Any additional code so that cells in A column remain populated but cells in B, C & D are inserted as blanks?

-Gary

 

[wilson187]

Any additional code so that cells in A column remain populated but cells in B, C & D are inserted as blanks?

-Gary

My mistake just changed the 4 to 1!

Exactly that I was looking for.

Thanks

 

[JoeMo]

My mistake just changed the 4 to 1!

Exactly that I was looking for.

Thanks

You are welcome - thanks for the reply.