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

Simon Lloyd

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
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

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

New member
Joined
Jun 13, 2011
Messages
4
Reaction score
0
Points
0
The locality code comes on top of each sheet & the same persons within the sheet have the same locality code.

Thank you.
 

Simon Lloyd

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
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
 

oeleong1969

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