+ Reply to Thread
Results 1 to 10 of 10

Copy range from one sheet to another and add a prefix

  1. #1
    Registered User
    Join Date
    07-17-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Copy range from one sheet to another and add a prefix

    I never used VBA before and was able to learn a little through searching for a solution to my issue. I have a worksheet that the end user load from a site to copy rows from their worksheet and paste to Sheet1 as follows:
    1. Column A: product id.
    2. Column B: date
    3. Column C: replacement product id
    4. Column D: description
    As the end user paste into this worksheet, I wanted the same to be copied to Sheet2 and on any change made in Sheet1. However, I wanted to add a prefix “~” to columns A and C in Sheet2.
    Can you please assist with how the code will look like?
    What I found working is the following code but don’t know how to control what is being copied to the second work sheet.
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sCell As String
    sCell = Target.Address
    Sheet2.Range(sCell) = Target.Value
    End Sub

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Copy range from one sheet to another and add a prefix

    Try this

    Please Login or Register  to view this content.
    I've split the Target range into single cells as it might be multi-celled and span several columns.

    For future reference, try to place any code you use inside code tags (the # button on the toolbar) - it just makes it a bit easier to read
    Martin

  3. #3
    Registered User
    Join Date
    07-17-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Copy range from one sheet to another and add a prefix

    This solution worked great, many thanks.

    May I ask for one more request, I would like the code to execute on saving or closing the file, how can I do this?

  4. #4
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Copy range from one sheet to another and add a prefix

    I assume that you need....

    Please Login or Register  to view this content.
    Paste the code into the ThisWorkbook tab.

  5. #5
    Registered User
    Join Date
    07-17-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Copy range from one sheet to another and add a prefix

    Martin,

    It worked well again, thanks.

    I tested two scenarios:
    1. I added records and saved; Sheet2 had a copy of the cells I added. Looks very good.
    2. I then cleared couple of rows and saved; it didn't clear the same in Sheet2.

    I would like for the second scenario, when clearing the rows and save, to clear them in Sheet2 as well. Please assist.

    Lastly, can the copy starts from row 2 as the first row contains column header?
    Last edited by samneva; 07-17-2011 at 05:25 PM. Reason: Forgot one item

  6. #6
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Copy range from one sheet to another and add a prefix

    OK, we need to clear Sheet2 and repopulate before saving.

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    07-17-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Copy range from one sheet to another and add a prefix

    It worked well, thanks again for you help.

  8. #8
    Registered User
    Join Date
    07-17-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Copy range from one sheet to another and add a prefix

    Hi,
    I came across another issue running the above code. The code does not run for all users due to a macro security related restriction. is there a way to have the code trusted and run bypassing the security issue. This is all running within our company's local network.

  9. #9
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Copy range from one sheet to another and add a prefix

    Not sure that there is anything that can be done with the macro itself - maybe not too surprising

    What you could do is to digitally sign it and then get you users to set up their machines to trust macros signed by you.

    You sign by choosing a certificate of the tools menu in the VBA editor. To set up a certificate, search for 'certificate' in Excel help.

  10. #10
    Registered User
    Join Date
    07-17-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Copy range from one sheet to another and add a prefix

    ok, thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1