+ Reply to Thread
Results 1 to 13 of 13

Macro for perfoming same data manipulation to monthly files to create a report

  1. #1
    Registered User
    Join Date
    11-09-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    20

    Macro for perfoming same data manipulation to monthly files to create a report

    Hi, I have to create monthly dashboard reporting on this set of data attached in Excel 2010
    For me to do this I
    1. Copy and paste all the countries data into one spreadsheet.
    2. Add a new column to show the different products grouped into one regardless of the version
    3.Then link to device type

    I then create a pivot table from this and show:

    a)Device Type by country and percentages
    b) How many days they were offline etc
    c) and different data reproting

    And then create this into a dashboard.

    However I am getting thie file with different months in each tab and doing this manually every months.

    Is there a macro where i can run to do the manuipuation and extracting for me every month?

    All help is genuially appreciated and regarded highly.

    Kind regards,

    Newbie
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro for perfoming same data manipulation to monthly files to create a report

    Try the attached one. It will you give the correct format so as to produce a PT
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    11-09-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Macro for perfoming same data manipulation to monthly files to create a report

    Also, Is there a way of consolidating all the excel sheets into one summary page. This is easier for me to do my manipulation of analysis?

    Again all help is very much appreciated ;-)

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro for perfoming same data manipulation to monthly files to create a report

    "consolidating all the excel sheets into one summary page"
    That was exactly the purpose of the code!

    I am now making an assumption
    You mean different workbooks in files? You have excel worksheets (in workbooks)-may be in one folder, or different folders, so you wish to bring in all the sheets from workbooks in to one, so that you can consolidate them in a summary as above.
    If this is your request, you need to close this thread and start a new one

  5. #5
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Macro for perfoming same data manipulation to monthly files to create a report

    Hello,
    This takes care of #1

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    11-09-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Macro for perfoming same data manipulation to monthly files to create a report

    Hi, thank you very much for your quick replies!!!

    I should have been more clearer and said I only wanted the country worksheets and not with the summary tab?

    Is that possible and is it also possible to not have any blanks?

    Also one I copy this macro in excel how would i run and view it? sorry am new and trying to learn as quick and fully as I can to do by myself in future ;-)

  7. #7
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro for perfoming same data manipulation to monthly files to create a report

    Newbie,
    Sorry it is not clear to me!

    You have different worksheet names with country. Which of the sheets are to be included in to the summary, i.e, Which country should be consolidate in the summary and which should be excluded.
    which blank cells(Columns, rows) you need to exclude.

    To run the code.
    Press Alt+F11, or go to developer- visual basics.
    You then see module-Press F5 to run it.

    OR,
    Press Alt+F8, then click edit- this will take you to the module as above.

  8. #8
    Registered User
    Join Date
    11-09-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Macro for perfoming same data manipulation to monthly files to create a report

    Hi again,

    sorry!

    What I would like to do is only copy the worksheets which have countries on the tabs and not the summary or %market growth tab into a new summary sheet and no blanks showing in between counties in the final sheet?

    Is it possible to explain how to manipulate the code so I can use it in another worksheet for simlar data?

    Thanks again!!

  9. #9
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro for perfoming same data manipulation to monthly files to create a report

    Newbie,
    Please see attached PT. I am sure you can fine tune the PT and you not do even worry about blank cells as you exclude blank cells from PT
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro for perfoming same data manipulation to monthly files to create a report

    The code exclude the following sheets
    Please Login or Register  to view this content.

  11. #11
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro for perfoming same data manipulation to monthly files to create a report

    Newebie,
    Look at column "A" of the summay sheet. It lists all the sheet names which are included in the summary sheet.

  12. #12
    Registered User
    Join Date
    11-09-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Macro for perfoming same data manipulation to monthly files to create a report

    AB33,

    that is brilliant!!! I will look at teh code and see how i can add to a new spreadsheet with the same tabs?
    In the code is there a way of detecting or putting in the sheet names?

  13. #13
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro for perfoming same data manipulation to monthly files to create a report

    Newbie,
    All you have to do is: Add any new sheet in the book and re-run the code. The code clears the old data and populate with the new ones. The code will include itself any new sheet name and new data.

    What the code does not do:Automatically add new data in the summary with out hitting the F5 key.
    You can also include the following code in the summary sheet, so where is change in the summary sheet, the PT will automatically refresh the data- so you do not need to keep refreshing the PT table.
    To put this code:
    Right click on the summary sheet- View code- copy and paste this code in the module. Please note this code not does not go in the standard module(unlike the main code). It goes in the summary code.
    Please Login or Register  to view this content.
    Please also note that you need to change this line of the code to suite your needs
    ("PivotTable1").
    It refers to the name of the PT.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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