Results 1 to 6 of 6

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    324
    Articles
    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.
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  3. #3
    Quote Originally Posted by Simon Lloyd View Post
    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.

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

    Thank you.

  5. #5
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    324
    Articles
    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
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  6. #6
    Quote Originally Posted by Simon Lloyd View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •