Consulting

Results 1 to 3 of 3

Thread: Generating a table of events based on a calendar on a different worksheet

  1. #1

    Generating a table of events based on a calendar on a different worksheet

    Hi,
    I have a work tracker that has several columns before I have a "calendar" which is on row 3 for all the dates of the work year for me.
    Underneath the dates I have letters corresponding to the people. That work in my office. There are 4 letters that I use and the letter to look for will. Be in cell K1 if the sheet named Main.

    What I want the code to do is to go though each of the rows I have information on and then look in each cell if there is a letter that matches the letter in K1.

    If it finds a letter match then I want the program to copy the information on cell column E row (whichever row the code is looking at) and paste it on sheet called VISITS and the way I tried to use the variable r to go down on the rows of the page VISITS

    CODE AS I HAVE IT

    Private Sub CommandButton9_Click()
    
    
    
    '   Will generate a table of visits for each rep
    
    
    '   First part hides rows that don't match the Stds Rep
    
    
    Dim b As Integer
    
    
    For b = 4 To 45
    
    
    If Cells(b, 3).Value = Range("K1").Value Then
    
        Sheets("Main").Rows(b & ":" & b).EntireRow.Hidden = False
    
    Else
    
        Sheets("Main").Rows(b & ":" & b).EntireRow.Hidden = True
    
    End If
    
    Next
    
    
    '   Finds the column that "today" is on.
    
    
     Dim a As Integer
    
            a = 106
    
        Do Until Cells(3, a).Value = Date
    
            Columns(a).EntireColumn.Hidden = True
    
             a = a + 1
    
        Loop
    
        
    
        Cells(3, a).Select
    
        
    
    Dim r As Integer
    
     r = 5
    
    
     '    look in a row
    
        For a = a To 416
    
            Cells(4, a).Select
    
            If ActiveCell.Value = Range("K1").Value Then
    
                Sheets("VISITS").Range("B" & r).Value = Sheets("Main").Range("E" & a).Value
    
                r = r + 1
    
        
    
            Else
    
            End If
    
    
        Next
    
    End Sub
    Thank you very much in advance.
    Last edited by Paul_Hossler; 10-27-2020 at 03:14 PM.

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    1. Welcome to the forum. Please take a minute and read the FAQs at the link in my sig

    2. I added CODE tags for you this time. We like to use them since it sets off macros and does some formatting

    3. Attaching an example workbook would be useful. Show what you have, and what you want to end up with
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3

    Adding a workbook example

    Apologies for the bad ettiquete, I was posting on my phone due to not being able to do it on the work computer.
    I am trying to upload the file i have the code in.

    What I am trying to do is in the end have a small table in the tab called visits (the headers are already there) which lists all the instances in which the letter is found on the workbook on the tab called Main.

    The table should have the name of the course (of that row) and the date of where the letter is found (would be the same column the letter is but at the top of the table with all the dates.

    I would have deleted the extra columns but didn't since they matter for my numbering purposes.

    The Command Button 9 in the macros is the one that I'm trying to make work. It is the large square button at the bottom. I have done (attempted) to have the code go through the first row of the calendar only and I have not done any steps to attempt the code go over the whole range of the "calendar"

    I hope this makes my end state more clear.

    Thank you again
    Attached Files Attached Files
    Last edited by Learning; 10-27-2020 at 04:21 PM.

Posting Permissions

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