Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2004
    Location
    Fargo, North Dakota, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Easy Excel Problem (Office XP)

    Greetings from a Newbie:

    This is likely very simple for several of you pros, but I am just learning VBA and could use a little jump start.

    My Emergency Department schedule is in Excel (Office XP). The ED schedule spreadsheet is very simple, one Master Schedule and 13 individual schedules that read the names off the Master and display only the individual partner's name on the appropriate shifts for that individual partner.

    I would like to add data verification / error handling with this goal: Add a new worksheet for each partner with vacation requests. Every cell entry on the Master would reference the Vacation request sheet for whichever partner is being added to the Master and pop up an error handling message if it conflicts. Data verification works great for ONE partner, but I can only figure out how to do this for one person, not 13. I believe that a simple Do Loop would do the trick and have written this type of simple Basic code before, but haven't figured out how to incorporate it into Excel. I'm slugging through a couple VBA online tutorials and making progress, but thought a little help might be in order.

    Thanks.
    Greg

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Easy Excel Problem (Office XP)

    Welcome to Woody's Lounge.

    I'm not sure I understand your description completely, but if your question is how to loop through a series of worksheets, the answer is that it depends on how you can identify those worksheets. Can the relevant worksheets can be distinguished from other worksheets by their name, or in another way?

  3. #3
    New Lounger
    Join Date
    Nov 2004
    Location
    Fargo, North Dakota, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Easy Excel Problem (Office XP)

    Yes, each sheet in the workbook has a separate name, such as "Bilstad Vacation", "Bjerke Vacation", etc. No problem referring to each cell on each of these sheets.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Easy Excel Problem (Office XP)

    OK, thanks. Do you want to loop through ALL sheets whose name ends in "Vacation", and ONLY those? If so, you can do it like this:

    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    If Right(ws.Name, 8) = "Vacation" Then
    ' Code to process worksheet goes here
    ' Refer to a cell on the worksheet as
    ' ws.Range("E3") or ws.Cells(3,5) etc.
    End If
    Next ws

  5. #5
    New Lounger
    Join Date
    Nov 2004
    Location
    Fargo, North Dakota, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Easy Excel Problem (Office XP)

    Thank you very much Hans.

    I appreciate the help and I'll give this a try.

    Regards,
    Greg

Posting Permissions

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