Copy paste manually

[PA]

I have the format in excel file as below:

Employee Number Location Payment Type Amount ($)
123 City RI $50
City RA $25
City RX $15

124 Brisbane RA $14
Brisbane RU $12


158 Perth RT $56
Perth RA
$11

…… and so on –reach thousand of lines.

If I wish to have this second format –per example below (in excel) , I do
need to copy and paste the Employee number to each row one by one.Is there
any way (any vb code /macro) that I can use for automation to get the second
report (as example below)?


Format as result of manual copy paste the Employee number as follows:

Employee Number Location Payment Type Amount ($)
123 City RI $50
123 City RA $25
23 City RX $15

124 Brisbane RA $14
124 Brisbane RU $12


158 Perth RT $56
158 Perth RA $11

…… and so on –reach thousand of lines.


Anyone can help and much appreciate any input /suggestion.

PA

 

[Gary Keramidas]

see chip pearson's response to "A script to fill in missing text" from
10/31

see if that's what you want. here is is post, but read the op's post, too.


Try the following code:


Dim LastRow As Long
Dim RowNdx As Long
Dim OldVal As String

LastRow = Cells(Rows.Count, "B").End(xlUp).Row
OldVal = Range("A1")
For RowNdx = 1 To LastRow
If Cells(RowNdx, "A").Value = "" Then
Cells(RowNdx, "A").Value = OldVal
Else
OldVal = Cells(RowNdx, "A").Value
End If
Next RowNdx

 

[PA]

Yes, many thanks.That's Great.

PA