Closed Thread
Results 1 to 5 of 5

Copy data based on multi criteria to another sheet and hide rows using range on sep sheet

  1. #1
    Registered User
    Join Date
    07-24-2012
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Red face Copy data based on multi criteria to another sheet and hide rows using range on sep sheet

    Hello,

    I am very new to Excel and cannot get my head around macros. It doesn’t help that I am also blonde!

    I am using MS Excel 2003.

    I understand that my approach is probably not the most efficient, but given that I have only tonight to solve this issue I guess I am stuck with it for now. Any assistance you could give me would be very much appreciated.

    Originally what I had been doing which worked quite well was:

    On sheet 2 “Data Entry” I had a column called ‘Check’ (column D) which contained the following formula:

    =COUNTIF(E7:BB7, "<>")

    This returned an incrementally increasing value if there was data in the row.

    The initial macro I wrote copied the entire sheet (which I renamed Report) and then ran a this little gem of a macro (which I found here) which hid the rows in Column D which contained a ‘0’ whilst leaving the headings (blue fill)


    Please Login or Register  to view this content.
    The initial problem I could not solve by myself was how to divide this up on a monthly, quarterly or annual basis.

    So I created the attached spreadsheet and as the check column now required more than just 1 column and it wouldn’t fit on a single spreadsheet, I created a ‘master’ validation sheet which contained the relevant formulas for the monthly, quarterly and annual splits across two main areas ‘theme’ – info in the drop down boxes or text in the w/ending month columns and ‘audience’ (located in blocks at the end of each month and corresponds to an entry in the theme section – or should).

    The key elements from the client’s perspective is that they wanted to be able to run a report for ‘theme’ and ‘audience’ for a single month (I was thinking current month) or for a quarter (starting from July 12) or annually, with the last option being the next 3 months from the current month.

    I would then run the hide rows macro for that month based on the information in the relevant column on the master validation sheet.

    That’s where I got stuck and don’t know where to from here.

    I have attached a sample spreadsheet minus sensitive data with some sample reports. Only one report tab allowed at any one time.

    The key sheets are:

    • Hide – Validation (which will be hidden)
    • Start here (not required for reporting – will link to relevant sections on the data entry sheet using a macro)
    • Data Entry
    • Publication Dates (not required for reporting – static data only)
    • Generate Report
    • Report (generated by macro from the Generate Report tab)

    TEST Spreadsheet - Excel Help v1.xls

    I am also having problems with the size of the file.

    Please let me know if you need more info.

    Really thank you – I mean it. Numbers are not my forte.

    Suzi
    Last edited by arlu1201; 07-24-2012 at 05:44 AM.

  2. #2
    Registered User
    Join Date
    07-24-2012
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Copy data based on multi criteria to another sheet and hide rows using range on sep sh

    Any ideas will be gratefully accepted. Even if you could just point me in the right direction to reference the appropriate column on the validation sheet for the correct period (ie month, qtr etc) and the correct syntax for the hide rows macro. At this stage, don't care if I need to duplicate the macro for each and every month, quarter or annually. Thanks heaps!

  3. #3
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Copy data based on multi criteria to another sheet and hide rows using range on sep sh

    Suzig,

    Welcome to the forum.

    I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  4. #4
    Registered User
    Join Date
    07-24-2012
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Copy data based on multi criteria to another sheet and hide rows using range on sep sh

    Apologies arlu1201. Thank you for the pointer. Will do in future!

  5. #5
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Copy data based on multi criteria to another sheet and hide rows using range on sep sh

    Welcome to the Forum, unfortunately:

    This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread. http://www.excelforum.com/excel-prog...worksheet.html

    Thread Closed.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1