1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel - how to reference a worksheet based on value of a cell

Discussion in 'Business Applications' started by todude, Dec 15, 2004.

Thread Status:
Not open for further replies.
  1. todude

    todude Thread Starter

    Joined:
    Dec 15, 2004
    Messages:
    2
    Hi all,

    I have a workbook containing 30 worksheets. The first sheet is named Master, while the other 29 worksheets are named with cities where we held branch offices (Los Angeles, New York, Chicago, Washington D.C., etc.)

    I am trying to write a macro that will copy the contents of a row in the Master sheet to a particular worksheet based on the contents of the cell in that row that contain the location.

    To illustrate:
    Master sheet:
    Columns are: Staff Name, Location, Project status

    We have over 5000 staff.
    I am trying to write a macro that will copy each staff's record to the worksheet corresponding to his/her location, depending on their project status.

    So I am planning to use macro buttons labelled A, B or C (these are the values in the Project status column). So when I click on the button labelled A, the macro will go through all 5000 employees, and for those employees whole project status is "A", will copy that employee's record into a worksheet depending on his/her location.

    For example:
    Staff name, Location, Project Status
    Joe Doe, Los Angeles, A
    Jane Davis, New York, A
    Mike Smith, Los Angeles, C

    When I click on the macro for "A", it should copy Joe Doe's record into the worksheet labelled Los Angeles and copy Jane Davis' record into the worksheet labelled New York.

    Thank you so much for your help. I've been searching all over the place for the command to use to reference the worksheet.
     
  2. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    For the love of PacMan, make sure you save your file BEFORE you try this. Try it on a copy of the file, even.

    HTH,
    Andy

    Sub A_Status()
    'select the list of staff names on Master sheet, then run
    For Each Cell In Selection
    If Cell.Offset(0, 2).Value = "A" Then
    DestinationSheet = Cell.Offset(0, 1)
    Cell.Resize(1, 3).Copy
    Sheets(DestinationSheet).Select
    'assumes each location sheet has a header row
    Range("A65536").End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste
    Sheets("Master").Select
    End If
    Next Cell
    Application.CutCopyMode = xlCopy
    End Sub
     
  3. todude

    todude Thread Starter

    Joined:
    Dec 15, 2004
    Messages:
    2
    Thanks a lot XL Guru. You made my day!
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/308168

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice