To copy a cell & paste it onto a column based on the name

[oeleong1969]

I would like to automate the process to copy a locality code on a cell to a column. I have attached the excel file which contains 2 sheets, one is the original sheet (named Original)which shown the Locality Code on top of the table & the other sheet (Resulted Copy) shown on which column the code has to be pasted.

Thank you very much.

[Simon Lloyd]

Would the column already exist?, how would you decide which person gets which locality code?, is there a seperate sheet for each locality code?

Why would you need to copy and paste? from what i see in your example you simple need =Original!D1 in all your blank cells in the locality column or if only using the original sheet then use =D1 and copy down.

If this doesn't suffice then you need to provide a sample workbook which is truly representative in data type and structure to your live workbook so we can help further.

[oeleong1969]

Would the column already exist?, how would you decide which person gets which locality code?, is there a seperate sheet for each locality code?

Why would you need to copy and paste? from what i see in your example you simple need =Original!D1 in all your blank cells in the locality column or if only using the original sheet then use =D1 and copy down.

If this doesn't suffice then you need to provide a sample workbook which is truly representative in data type and structure to your live workbook so we can help further.

Actually, I have about 2000 pages of the same repetitive sequel of the names & so on. It would take a hell lot of tedious work to complete it. So, I hope that this can be automate through Macro. Thank you for your reply.

[oeleong1969]

The locality code comes on top of each sheet & the same persons within the sheet have the same locality code.

Thank you.

[Simon Lloyd]

Try this:

Sub loacale_to_column()
Dim sh As Worksheet, rng As Range
For Each sh In Sheets
sh.Range("G3").Value = "Locality Code"
Set rng = sh.Range("A4:A" & sh.Range("A" & Rows.Count).End(xlUp).Row)
On Error GoTo Nxt
With rng.SpecialCells(xlCellTypeConstants)
.Offset(0, 6) = sh.Range("D1").Value
End With
Next sh
Nxt:
End Sub

[oeleong1969]

Try this:

Sub loacale_to_column()
Dim sh As Worksheet, rng As Range
For Each sh In Sheets
sh.Range("G3").Value = "Locality Code"
Set rng = sh.Range("A4:A" & sh.Range("A" & Rows.Count).End(xlUp).Row)
On Error GoTo Nxt
With rng.SpecialCells(xlCellTypeConstants)
.Offset(0, 6) = sh.Range("D1").Value
End With
Next sh
Nxt:
End Sub

Dear Simon,

The code works well.
Thank you very much.