+ Reply to Thread
Results 1 to 5 of 5

Create a report from the Data sheet

  1. #1
    Registered User
    Join Date
    12-01-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    92

    Create a report from the Data sheet

    Dear Excel Gurus:

    I have an Excel file with 2 tabs, DATA sheet and REPORT sheet. I need help to create a VBA code to come up with a report taken from the DATA sheet. Right now what Iím doing is go to the Data sheet and filter on the Item Number, then copy the filtered data to a new sheet and remove all the columns that I donít need. What I want to accomplish is on the REPORT sheet, I will just type the ITEM number on cell A2, it will list down all the corresponding data.

    DATA sheet details
    Column E: Trans Date
    Column F: Item Number
    Column H: TR Type
    Column I: TR Qty
    Column J: Bin
    Column K: Level
    Column N: Comments

    REPORT sheet details
    Row A2 will be the look up. When I type an Item No. on this cell, it will list down all the item that matches and will display all rows as follows:
    RESULT
    Row A6: Trans Date
    Row B6: TR Type
    Row C6: TR Qty
    Row D6: Bin
    Row E6: Level
    Row F6: Comments

    Thank you in advance for your great help.

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Create a report from the Data sheet

    Put this in the Worksheet module for REPORT:
    Please Login or Register  to view this content.
    Last edited by Olly; 09-11-2014 at 11:05 AM.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    12-01-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    92

    Re: Create a report from the Data sheet

    Thanks for the quick response.

    Tried and tested and it is working but a little bit slow. I have around 4000 rows and it will grow in time.

    Here are my observation and needs revision on the code.
    1. I forgot to mention that the both sheets are protected and cells are locked; revise to unprotect then protect. It gets error on the protected sheet.
    2. Cells on the Data sheets are conditionally formatted and it copied the formatted cells. Revise just to copy the values only without conditionally formatted.

    Thank you again.

  4. #4
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Create a report from the Data sheet

    Okay, so a couple of minor tweaks:
    - unprotect sheets before / reprotect sheets after
    - paste values
    - turn off screenupdating / events, to help speed things up a bit

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    12-01-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    92

    Re: Create a report from the Data sheet

    Thanks for the quick response.

    It is working but stiil slow, however it can do the job for now...

    Can you add another filter for the TRANS DATE, so the filter will be TRANS DATE and ITEM NUMBER.

    Thanks a lot...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Help to create excel sheet in report.exe
    By deepanc in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-14-2013, 10:30 AM
  2. How to create report on sepearte sheet
    By mchavoya_perez in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-25-2011, 02:38 AM
  3. Replies: 2
    Last Post: 06-29-2011, 04:35 PM
  4. Need to create report from sheet
    By tcopling in forum Excel General
    Replies: 0
    Last Post: 05-03-2011, 01:58 PM
  5. [SOLVED] create report in excel sheet
    By AskExcel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-08-2006, 02:15 PM

Tags for this Thread

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