none
Excel Macro RRS feed

  • Question

  • Hi,

    I am new to excel macro and I like to do the following:

    1. Extract and copy all data from an excel Daily or weekly report
    2. Sort Data by Category
    3. Create new worksheet with each category (worksheet needs to be renamed by category)
    4. In each worksheet: Highlight each row with data containing past today's date

    Thank you in advance.

    by the way I am using Excel 2010
    Sunday, December 11, 2016 10:24 AM

All replies

  • B,
    Re:  "would like to do"

    Try recording a macro while doing some/all of the above.
    That will give you "starter" code.
    In Excel help (not vba help), search for "create a macro".

    Note that in these help forums, you will get the best response by asking a specific question...
      For example:  How do I determine area on a worksheet that contains data?
      Possible answer...
        Dim rng as Range
        Set rng = Worksheets("Daily Report").UsedRange
        -Or-
        Dim strWhere as String
        strWhere = Worksheets("Daily Report").UsedRange.Address

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Sunday, December 11, 2016 2:10 PM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,
    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Monday, December 12, 2016 7:59 AM
  • Hi,

    To record a macro, you could visit Automate tasks with the Macro Recorder. It could record most manipulations.

     

    >>1.Extract and copy all data from an excel Daily or weekly report

    You could use Workbooks.Open Method (Excel) to open workbook.

    If you want to copy the whole sheet, you could use Worksheet.Copy Method (Excel)

    If you want to copy specific range, you could use Range.Copy Method (Excel)

     

    >>2.Sort Data by Category

    To sort data, you could use Sort Object (Excel)

     

    >>3.Create new worksheet with each category (worksheet needs to be renamed by category)

    To create new worksheet, please visit Sheets.Add Method (Excel).

    Then you could use Worksheet.Name Property (Excel) to rename the sheets.

     

    >>4.In each worksheet: Highlight each row with data containing past today's date

    You could use Conditional Formatting. Please visit Use a formula to apply conditional formatting

    In VBA, use FormatCondition Object (Excel).

     

    If you have any issues, I suggest you post new thread with specific questions.

    Thanks for your understanding.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 13, 2016 7:35 AM
    Moderator