+ Reply to Thread
Results 1 to 5 of 5

Copy data from a worksheet and paste to worksheet with a similar worksheet name

  1. #1
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,166

    Copy data from a worksheet and paste to worksheet with a similar worksheet name

    I have a number of worksheets that follow the same naming format. I want a macro that will copy information from the "Client" sheet and paste the information to the "Agent" sheet. I don't want to write 25 different different macros to accomplish this. For example, if the active sheet is "Branch_550_Client", I want to copy information to sheet "Branch_550_Agent". If in 788_client, copy to 788_agent; and so on. Is there a way to do this? (I am willing to change the sheet name in properties to "112_Client", "Client_112", or any other combination if that makes it easier).

    I have 25 sheets named as follow (first 5 sheets only)
    Branch_112_Client
    Branch_300_Client
    Branch_550_Client
    Branch_612_Client
    Branch_788_Client

    I have 25 sheets named as follows (first 5 sheets only)
    Branch_112_Agent
    Branch_300_Agent
    Branch_550_Agent
    Branch_612_Agent
    Branch_788_Agent

    I have the macro as follows (assume activesheet is "Branch_112_Client"):
    Please Login or Register  to view this content.
    Last edited by zbor; 10-21-2012 at 03:31 AM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Select New Worksheet VBA

    Didn't test it but try something like this:

    Please Login or Register  to view this content.
    Last edited by zbor; 10-21-2012 at 03:15 AM.

  3. #3
    Registered User
    Join Date
    10-15-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2003/2010
    Posts
    22

    Re: Select New Worksheet VBA

    Try this code,
    Please Login or Register  to view this content.
    The code will loop through all your worksheets. If the word "Client" is found it its name action will be taken.
    1. The the last 6 characters from its name (hopefully, "Client") will be truncated and replaced with "Agent".
    2. Rows 14:LastRow will be copied and pasted to A1 in the Agent's sheet.
    If the Agent's sheet doesn't exist an error will occur.

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Select New Worksheet VBA

    Hi, maacmaac,

    if you only want to copy the information from one worksheet at a time:
    Please Login or Register  to view this content.
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  5. #5
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,166

    Re: Select New Worksheet VBA

    Thanks for all comments. This will work just fine.

+ 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