how do paste same data in every other line
This macro should do what you want...
Sub InsertData()
Dim X As Long
Dim EndRow As Long
Dim NewEndRow As Long
Const ColNum As Long = 1
Const StartRow As Long = 2
Const NewDataLineCount As Long = 3
EndRow = ActiveSheet.Cells(Rows.Count, ColNum).End(xlUp).Row
NewEndRow = (EndRow - StartRow) * (NewDataLineCount + 1) + StartRow
For X = EndRow To StartRow Step -1
With Cells(NewEndRow - (NewDataLineCount + 1) * (EndRow - X), ColNum)
.Value = Cells(X, ColNum).Value
If X < EndRow Then
.Offset(1, 0).Value = ""
.Offset(2, 0).Value = "S"
.Offset(3, 0).Value = "E"
End If
End With
Next
End Sub
Set the column number where your data is located (assumed to be 1 for this
example) in the ColNum constant, the starting row for your data (assumed to
be 2 for this example) in the StartRow constant, the number of lines of new
data you will be inserting in the NewDataLineCount constant (3 as per your
posting) and set the new lines of data to be inserted in the If-Then block
of code via the Offset properties as shown. The code is general and can be
changed as needed by setting the three constants just mentioned and setting
the proper number of Offset statements to match the data to be inserted.
Rick
"ben0209" wrote in message
...
Afternoon all
I am looking at a data table comprising 1 column x multiple rows. I would
like to be able to insert between every entry (all different) the sequence
of:
DATA
blank row,
cell containing 'S'
cell containing 'E'
NEXT DATA.
I have seen some threads and can insert three blank rows but am falling
down
at getting the specific texts in the cells.
Any help would be appreciated.
|