+ Reply to Thread
Results 1 to 13 of 13

Macro to prepare Database from Shipment Details Sheet

  1. 09-01-2014, 09:26 AM #1
    prkhan56
    prkhan56 is offline
    Forum Contributor
    Join Date
    06-03-2004
    Location
    India
    MS-Off Ver
    2013
    Posts
    205

    Macro to prepare Database from Shipment Details Sheet

    Hello All,
    I am using Excel 2010
    I have problem in creating a Pivot Report from the Data Set I have with me (See Before Sheet)
    I need a Macro to make me a Database so that I can create a Pivot Report (See After Sheet)

    BEFORE SHEET
    Store name - Cell B4
    Data -Cells B4:Z42 covering 12 Months
    Data Set is upto Row 642. Height of Each the Data Set is 39 (See Helper Column in AC).
    Month names – Cell C5 to Z5 (merged for each month)
    (Sample for one Set) – Data Set is upto 642 Rows


    AFTER SHEET
    Col A should show STORENAME
    Col B should show ITEMS
    Col C should show MONTH
    Col D should show YEAR
    Col E should show DATA
    (Shown for Jan Month only) – it should put data till the last month

    Thanks in advance.
    Rashid
    Attached Files Attached Files
    Last edited by prkhan56; 09-12-2014 at 02:39 PM.
    Reply With QuoteRegister To Reply
  2. 09-01-2014, 10:49 AM #2
    rcm
    rcm is offline
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro to prepare Database from Shipment Details Sheet

    Hoping this helps...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 09-03-2014, 03:09 PM #3
    prkhan56
    prkhan56 is offline
    Forum Contributor
    Join Date
    06-03-2004
    Location
    India
    MS-Off Ver
    2013
    Posts
    205

    Re: Macro to prepare Database from Shipment Details Sheet

    Hello,
    Sorry for the delay. Had some problem with me Laptop
    Thank you very much for your code.
    But if you see the 'After' Sheet eg Row No.263 to Row 281 - it is also adding the TOTAL UNITS, which is not required.
    For other stores also TOTAL UNITS is showing.

    I just need the data and not the Total Units.
    Secondly I noticed that you have typed some characters viz s, t in Col A.
    My original file does not have this...
    Is it compulsory for me to type this in Col A for the macro to run successfully?
    As I tested the macro after deleting data from Col A. It did not do anything.
    So please look into this and rectify for me
    Thanks once again for your valuable time and support.
    Regards
    Reply With QuoteRegister To Reply
  4. 09-06-2014, 01:02 PM #4
    prkhan56
    prkhan56 is offline
    Forum Contributor
    Join Date
    06-03-2004
    Location
    India
    MS-Off Ver
    2013
    Posts
    205

    Re: Macro to prepare Database from Shipment Details Sheet

    Any further help please?
    Reply With QuoteRegister To Reply
  5. 09-06-2014, 01:33 PM #5
    rcm
    rcm is offline
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro to prepare Database from Shipment Details Sheet

    The reason that I needed those flags (characters) was to exclude the row . Ill devise another way without the characters

    Here is the macro that does not need the flags and omits the total summary
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 09-07-2014, 01:32 AM #6
    prkhan56
    prkhan56 is offline
    Forum Contributor
    Join Date
    06-03-2004
    Location
    India
    MS-Off Ver
    2013
    Posts
    205

    Re: Macro to prepare Database from Shipment Details Sheet

    Thanks
    will test and revert
    Regards
    Reply With QuoteRegister To Reply
  7. 09-07-2014, 03:27 PM #7
    prkhan56
    prkhan56 is offline
    Forum Contributor
    Join Date
    06-03-2004
    Location
    India
    MS-Off Ver
    2013
    Posts
    205

    Re: Macro to prepare Database from Shipment Details Sheet

    Hello,
    Thanks for the code.
    I tested it and found some problems.
    Please refer to the After sheet in the attached sample Column F...I have marked in green color.
    Sorry I forgot to mention in my original post that some Columns are hidden (which I have unhidden in the attached file)
    Thanks once again for your time and support
    Regards
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 09-09-2014, 05:16 AM #8
    prkhan56
    prkhan56 is offline
    Forum Contributor
    Join Date
    06-03-2004
    Location
    India
    MS-Off Ver
    2013
    Posts
    205

    Re: Macro to prepare Database from Shipment Details Sheet

    Any further help on the problem please?
    Regards
    Reply With QuoteRegister To Reply
  9. 09-09-2014, 10:53 PM #9
    rcm
    rcm is offline
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro to prepare Database from Shipment Details Sheet

    Sorry, I misplaced a coditional statement and was not performing well when there was a 0 entry. I fixed it!!!
    Attached Files Attached Files
    Last edited by rcm; 09-09-2014 at 10:54 PM. Reason: typo
    Reply With QuoteRegister To Reply
  10. 09-10-2014, 10:51 AM #10
    prkhan56
    prkhan56 is offline
    Forum Contributor
    Join Date
    06-03-2004
    Location
    India
    MS-Off Ver
    2013
    Posts
    205

    Re: Macro to prepare Database from Shipment Details Sheet

    Wow Brilliant! Works great!
    Just a small thing if you can fix for me please.

    I will run the macro from Customer Summary sheet
    It should create a new sheet 'After' and put the results there.
    Thanks a million for all your help and time.
    Reply With QuoteRegister To Reply
  11. 09-11-2014, 08:34 AM #11
    rcm
    rcm is offline
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro to prepare Database from Shipment Details Sheet

    I added a button in the Customer Summary Sheet at the top and assigned the macro to it.

    As for the After tab, the macro will clean it before filling it in...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 09-12-2014, 02:40 PM #12
    prkhan56
    prkhan56 is offline
    Forum Contributor
    Join Date
    06-03-2004
    Location
    India
    MS-Off Ver
    2013
    Posts
    205

    Re: Macro to prepare Database from Shipment Details Sheet

    Thanks you are a star
    I have marked the post as Solved.
    Reply With QuoteRegister To Reply
  13. 09-12-2014, 03:24 PM #13
    rcm
    rcm is offline
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro to prepare Database from Shipment Details Sheet

    Any time, I just work on the forum problems at night so my response is not constant
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro copy details from certain cells on one sheet to another
    By rjm87 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-22-2013, 10:31 PM
  2. I would like to prepare a excel containing annual leave details as well as monthly details
    By Priti Dha in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-21-2012, 01:19 AM
  3. Extract Field Details From Database
    By mohan.r1980 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-18-2011, 07:36 AM
  4. prepare excel file for Access database
    By cazper67 in forum Excel General
    Replies: 1
    Last Post: 08-16-2010, 05:46 AM
  5. Help to prepare a database with data from 12 different sheets in a specific sequence.
    By theexcelnovice in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-12-2009, 06:10 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1