+ Reply to Thread
Results 1 to 12 of 12

Match copy and paste on multiple worksheets

  1. #1
    Registered User
    Join Date
    05-31-2018
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    34

    Question Match copy and paste on multiple worksheets

    So I've been trying to make a macro that will check names against a data base and auto fill in their respective industry team. The list is sent to us in a certain format every month as shown below. if the name doesn't match anything in the data base I was trying to get it to highlight the row in red. Below are screen shots of the work sheet and the macro i attempted. I keep on getting error 13 mismatch. Major problem is the format of the list as youll see below

    Screenshot (2).png

    Screenshot (3).png

    The macro I worked on

    Sub checkcrmgmt()

    Dim employee As String
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim sh3 As Worksheet

    Set sh1 = ActiveWorkbook.Sheets("Cover")
    Set sh2 = ActiveWorkbook.Sheets("List")
    Set sh3 = ActiveWorkbook.Sheets("mgmt")

    sh2.Range("C2:C150").ClearContents

    employee = sh2.Range("b2:b150")

    For i = 5 To 1000
    For C = 2 To 10000
    For E = 5 To 150
    If sh3.Cells(i, C) = sh2.Cells(E, 2) Then
    sh3.Range(Cells(i, 1).End(xlUp)).Copy
    sh2.Range(E, 3).PasteSpecial x1pasteformulasandnumberformats
    Else
    Rows(E).Interior.ColorIndex = 3
    End If
    Next E
    Next C
    Next i

    End Sub

    Screenshot (5).png

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,776

    Re: Match copy and paste on multiple worksheets

    It would be easier to help and test a possible solution if you could attach a copy of your file instead of pictures. Explain in detail what you want to do using a few examples from your data and referring to specific cell, rows, columns and sheets.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    05-31-2018
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    34

    Re: Match copy and paste on multiple worksheets

    Attached below
    Attached Files Attached Files

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,776

    Re: Match copy and paste on multiple worksheets

    Please explain step-by-step in detail what you want to do using a few examples from your data and referring to specific cell, rows, columns and sheets.

  5. #5
    Registered User
    Join Date
    05-31-2018
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    34

    Re: Match copy and paste on multiple worksheets

    On the list page I input the information I want the macro to check the names in column B to the names on the mgmt worksheet, and auto fill the industry team column C if it doesnt find any names in the mgmt worksheet highlight the row in red. all the double letters on the mgmt sheet are names.

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,776

    Re: Match copy and paste on multiple worksheets

    Try:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    05-31-2018
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    34

    Re: Match copy and paste on multiple worksheets

    Its only working on the MD list if I input a name from the VP, associate etc list it doesnt seem to pick up. also when repeating the macro when you put a correct name would it be possible to un highlight to row?

  8. #8
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,776

    Re: Match copy and paste on multiple worksheets

    Try this version:
    Please Login or Register  to view this content.
    Could you please explain what you mean by
    Its only working on the MD list if I input a name from the VP, associate etc list it doesnt seem to pick up.

  9. #9
    Registered User
    Join Date
    05-31-2018
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    34

    Re: Match copy and paste on multiple worksheets

    So currently if I put DU it'll highlight in red instead of mark him as "Insurance/Internal". DU is in K17 on the mgmt sheet also one other thing is there a way to get a prompt telling you how many mismatches occurred?

  10. #10
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,776

    Re: Match copy and paste on multiple worksheets

    The only way I could get it to work is by filling in the blank rows in column A of the "mgmt" sheet. Try the attached file.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    05-31-2018
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    34

    Re: Match copy and paste on multiple worksheets

    Wow! The macro works perfectly

    Just one thing is there a way to make a macro to auto fill the empty space and only keep the main teams deleting the extra stuff (for example "toronto"). the full list is in the file below.

    Head (Global)
    Insurance/Internal
    Funds and Lines
    FI Europe/Latin America
    FI/NA/Asia/Middle East
    Soverreign Risk
    Policy Group
    Head GCC USA
    Funds & Hedge Funds (NY)
    TDBNA Investment
    GCC TD Singapore
    GCC TD Ldn
    Global Client
    Documentation
    Client Strategy
    Regulatory
    Attached Files Attached Files

  12. #12
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,776

    Re: Match copy and paste on multiple worksheets

    Try the attached file.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Copy + Paste from multiple worksheets
    By AlexShip in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-12-2017, 05:32 PM
  2. Copy and paste in multiple worksheets
    By malesela in forum Excel General
    Replies: 4
    Last Post: 12-26-2013, 09:54 AM
  3. [SOLVED] Copy/Paste from partial match between worksheets
    By ussenterprise in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 06-19-2013, 02:20 PM
  4. Copy/Paste Range of Data from Multiple Workbooks/Worksheets to Master Workbook/Worksheets
    By NumberCruncher311 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-19-2013, 08:21 PM
  5. Search, copy and paste across multiple worksheets
    By farkinell in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-14-2011, 05:22 AM
  6. Copy and Paste between worksheets on ID match
    By dems in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-11-2010, 01:06 PM
  7. Copy and paste btw multiple worksheets while maintaining one value
    By s_hillyard in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-23-2008, 01:39 PM

Tags for this Thread

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