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

oeleong1969

New member
Joined
Jun 13, 2011
Messages
4
Reaction score
0
Points
0
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.
 

Attachments

  • Copy to column.xls
    21.5 KB · Views: 18
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.
 
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.
 
The locality code comes on top of each sheet & the same persons within the sheet have the same locality code.

Thank you.
 
Try this:
Code:
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
 
Try this:
Code:
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.
 
Back
Top