Consulting

Results 1 to 9 of 9

Thread: Need a Macro to do Daily task

  1. #1

    Need a Macro to do Daily task

    Hi there,

    I have a some small problem. Please look in to my query and if possible please show me a solution.

    Here is a excel sample that i attached.

    It contains data relates to Some cources, it has been collection every week in a month.

    Here what i need to Filter the Excel with string "Main" on Column "E" then Copy the data in to Output sheet, as shown in the excel file. Then Go to "Master" Sheet then Filter on Column "E" with the strigns "Secondary and Third" then Filter on Column "F" with String "Main" then copy the data and paste it into Output Sheet as shown in the Excel. and For G Column viceversa.

    I know we can do manually, but I need this to do every day to prinout the data and I have 100's of similar tasks. So, I need a macro to this task.

    I try to figured it out but my thumbs down,.

    Can any one help me on this?

    Waiting for your kind reply.
    Attached Files Attached Files
    Happiness keeps u sweet, trials keep u strong, sorrow keeps u human, failure keeps u humble, success keeps u glowing, but only God keeps u going

  2. #2
    Is this is a Hard Task or My Instructions are not clear?
    Happiness keeps u sweet, trials keep u strong, sorrow keeps u human, failure keeps u humble, success keeps u glowing, but only God keeps u going

  3. #3
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Don't follow all your requirements, but here's a starter for you.

    [VBA]Option Explicit

    Sub Test()

    Dim wsM As Worksheet
    Dim wsO As Worksheet
    Dim Rng As Range
    Dim tgt As Range



    Set wsM = Sheets("Master")
    Set wsO = Sheets("Sheet3")
    Set Rng = wsM.Range("A1").CurrentRegion

    wsM.Range("$D:$D").AutoFilter Field:=1, Criteria1:="Main"
    Intersect(Rng, wsM.Range("$A:$C")).SpecialCells(xlCellTypeVisible).Copy wsO.Range("A2")
    wsM.Range("$D:$D").AutoFilter
    wsO.Range("A1") = "Main Course Interested"

    wsM.Range("$F:$F").AutoFilter Field:=1, Criteria1:="Main"
    Set tgt = wsO.Cells(Rows.Count, 1).End(xlUp).Offset(6)
    Intersect(Rng, wsM.Range("$A:$C")).SpecialCells(xlCellTypeVisible).Copy tgt
    wsM.Range("$F:$F").AutoFilter
    tgt.Offset(-1) = "You may also be intersted in:"

    End Sub
    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  4. #4
    Quote Originally Posted by mdmackillop
    Don't follow all your requirements, but here's a starter for you.

    [vba]Option Explicit

    Sub Test()

    Dim wsM As Worksheet
    Dim wsO As Worksheet
    Dim Rng As Range
    Dim tgt As Range



    Set wsM = Sheets("Master")
    Set wsO = Sheets("Sheet3")
    Set Rng = wsM.Range("A1").CurrentRegion

    wsM.Range("$D:$D").AutoFilter Field:=1, Criteria1:="Main"
    Intersect(Rng, wsM.Range("$A:$C")).SpecialCells(xlCellTypeVisible).Copy wsO.Range("A2")
    wsM.Range("$D:$D").AutoFilter
    wsO.Range("A1") = "Main Course Interested"

    wsM.Range("$F:$F").AutoFilter Field:=1, Criteria1:="Main"
    Set tgt = wsO.Cells(Rows.Count, 1).End(xlUp).Offset(6)
    Intersect(Rng, wsM.Range("$A:$C")).SpecialCells(xlCellTypeVisible).Copy tgt
    wsM.Range("$F:$F").AutoFilter
    tgt.Offset(-1) = "You may also be intersted in:"

    End Sub
    [/vba]
    Excellent and thank you very much. But I didn't see the data from Column F?
    Happiness keeps u sweet, trials keep u strong, sorrow keeps u human, failure keeps u humble, success keeps u glowing, but only God keeps u going

  5. #5
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    You should be able to do this from the sample posted. You don't show clearly what is filtered and where results go.
    If you get stuck, please post your best attempts.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  6. #6
    Quote Originally Posted by mdmackillop
    You should be able to do this from the sample posted. You don't show clearly what is filtered and where results go.
    If you get stuck, please post your best attempts.
    Hi,

    Your code works very well. But it ignores the Column "E". Please check the attached Excel file.

    Ouput sheet is the results i want.
    Sheet3 is the results that I got from your code.

    Thanks,
    Attached Files Attached Files
    Happiness keeps u sweet, trials keep u strong, sorrow keeps u human, failure keeps u humble, success keeps u glowing, but only God keeps u going

  7. #7
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Quote Originally Posted by krishhi
    Is this is a Hard Task or My Instructions are not clear?
    Its a little hard to test given that "Some Date" "Some Description", & "Some Course duration" are applied to all. Are we picking up the correct objects with the current code???? What about posting yet another workbook, with real dates, real course descriptions, and real course durations and we can test it again?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  8. #8
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Please refer to our FAQ, and my last response. We are here to assist, not do your work.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  9. #9
    Quote Originally Posted by mdmackillop
    Please refer to our FAQ, and my last response. We are here to assist, not do your work.
    Very sorry if you take my reply in a wrong way.
    Happiness keeps u sweet, trials keep u strong, sorrow keeps u human, failure keeps u humble, success keeps u glowing, but only God keeps u going

Posting Permissions

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