"Excelling in Excel" workshop

67 %
33 %
Information about "Excelling in Excel" workshop

Published on May 10, 2016

Author: atenevaj

Source: slideshare.net

1. Excelling in Excel For Each Object in Collection loop Angelina Teneva

2. Getting ready to VBA # showing the developer tab # VBE settings: Alt+F11  Tools  Options Angelina Teneva 2

3. Available Excel Objects: Where should I put my code? Sheet • Code will be available in this workbook onlyThisWorkbook • You will be able to use the code between 1 or more workbooks (as many as you declare in your code) Module • Code will be available in any workbook you openPERSONAL Especially important for Event procedures; event procedure that has been put in the wrong place will not work; and no error messages will be displayed either Angelina Teneva3

4. Why Declare? Where? Angelina Teneva  it is more PRECISE  minimizes error possibility – esp. when working with multiple tabs and workbooks  Saves time:  when you are first writing the code (esp. true for pivot table macros)  if you have to edit the code as a result of other changes in the worksheet  Makes your code easier to follow, understand (and revisit after a long period of time) Before 1st sub = to be available in all subs in module In each sub = this sub only 4

5. When declaring, DO have in mind the hierarchy of objects Application Workbook Worksheet PivotTables/ Charts/Ranges Dim Wks as Worksheet Dim PT as PivotTable Dim Cell As Range For Each Wks in ActiveWorkbook.Worksheets Wks.Activate For Each PT in ActiveSheet.PivotTables ----------------------------------------------------------------- Next PT For Each Cell in ActiveSheet.Range(“A:A”) ----------------------------------------------------------------- Next Cell Next Wks Angelina Teneva5

6. Angelina Teneva 6 For Each Cell in Range Loop

7. Converting Negative values into Positive 7 Convert Formulas to values for non-adjacent ranges

8. Fixing mismatching cell values Angelina Teneva8

9. Formatting cells Angelina Teneva9 # Fix text strings formatted in scientific format # Round values and truncate strings

10. Change Cell Colour (conditional formatting) for a different column /row Angelina Teneva10 Very handy if you’ve got multiple color coding options for a cell whose coloring depends on the value of another cell

11. Removing/Adding Hyperlinks # remove hyperlinks # add hyperlinks to e-mails by Angelina Teneva 11

12. Having to continually reset multiple pivot tables? Feeling sick at the sight of them? Chill… here’s how to quickly handle them with VBA For Each Pivot Table in Workbook loop A N G E L I N A T E N E V A 12

13. Aligning source data range Refreshing all pivot tables at once A N G E L I N A T E N E V A Can be referred to by using sheet and pivot table names, too 13

14. Adding/Removing Fields & changing position & caption NB! Don’t forget to close the loop A N G E L I N A T E N E V A 14

15. Adding data fields & changing calculation types R E G U L A R F I E L D ( D E C L A R E D ) R E G U L A R F I E L D ( N O T D E C L A R E D ) A N G E L I N A T E N E V A 15

16. Changing PT summary functions & number formatting I f t h e r e i s 1 f i e l d i n v a l u e s a r e a A N G E L I N A T E N E V A 16 I f t h e r e a r e m u l t i p l e f i e l d s i n t h e v a l u e s a r e a

17. Adding Calculated Fields Summary A N G E L I N A T E N E V A 17

18. Adjusting pivot fields’ settings NB! Don’t forget to close the loop A N G E L I N A T E N E V A 18

19. Filter a pivot table by using the report filter (1) S I N G L E C R I T E R I O N Assuming there has been no previously set filters If there had been, you need to use “PF.ClearAllFilters” before trying to apply any M U L T I P L E C R I T E R I A ( 1 ) & ( 2 ) A N G E L I N A T E N E V A 19

20. Filter a pivot table by using the report filter (2) F O R A V A L U E F R O M A D R O P D O W N F O R A U S E R D E F I N E D V A L U E A N G E L I N A T E N E V A 20

21. xlCaption corresponds to Label Filter! IF you choose Value filter, it will filter the values in DataBodyRange For Caption Filters to work, the field to be filtered must either be with Column or Row Orientation A N G E L I N A T E N E V A Using Pivot tables caption filters* 21

22. Using Value Filters UK Region (All) Year 2006 Sum of Visits (000) Quarter Purpose Quarter 1 Quarter 2 Quarter 3 Quarter 4 Grand Total Business 9,482 11,066 10,947 11,040 42,535 Holiday 7,270 14,462 18,285 9,239 49,257 Other 2,731 3,040 4,911 3,396 14,078 VFR 10,161 11,753 15,195 11,773 48,881 Grand Total 29,644 40,321 49,338 35,448 154,752 NB!! Value filter always looks at Grand Total if PT is in this layout If you want it applied to particular items, bring both “Quarter” and “Purpose” fields to either Row Area or Column Area A N G E L I N A T E N E V A 22

23. .PivotFilters.Add Type:=xlCaptionEquals .PivotFilters.Add Type:=xlCaptionDoesNotEqual .PivotFilters.Add Type:=xlCaptionBeginsWith .PivotFilters.Add Type:=xlCaptionEndsWith .PivotFilters.Add Type:=xlCaptionContains .PivotFilters.Add Type:=xlCaptionDoesNotContain .PivotFilters.Add Type:=xlCaptionIsGreaterThan .PivotFilters.Add Type:=xlCaptionIsLessThan .PivotFilters.Add Type:=xlCaptionIsBetween .PivotFields( “PO").ClearLabelFilters .PivotFilters.Add Type:=xlValueEquals .PivotFilters.Add Type:=xlValueDoesNotEqual .PivotFilters.Add Type:=xlValueBeginsWith .PivotFilters.Add Type:=xlValueEndsWith .PivotFilters.Add Type:=xlValueContains .PivotFilters.Add Type:=xlValueDoesNotContain .PivotFilters.Add Type:=xlValueIsGreaterThan .PivotFilters.Add Type:=xlValueIsLessThan .PivotFilters.Add Type:=xlValueIsBetween .PivotFields( “PO").ClearValueFilters Available Label and Value filters A N G E L I N A T E N E V A 23

24. .PivotFilters.Add Type:=xlDateLastWeek .PivotFilters.Add Type:=xlDateNextWeek .PivotFilters.Add Type:=xlDateThisWeek .PivotFilters.Add Type:=xlDateLastMonth .PivotFilters.Add Type:=xlDateThisMonth .PivotFilters.Add Type:=xlDateNextMonth .PivotFilters.Add Type:=xlDateLastYear .PivotFilters.Add Type:=xlDateThisYear .PivotFilters.Add Type:=xlAllDatesInPeriodJuly .PivotFilters.Add Type:=xlBefore, Value1:="14/03/2011“ .PivotFilters.Add Type:=xlAfter, Value1:="14/03/2011“ .PivotFilters.Add Type:=xlDateBetween, Value1:="14/03/2011", Value2:= _ "20/03/2011“ .PivotFilters.Add Type:=xlDateToday .PivotFilters.Add Type:=xlDateYesterday PIVOT TABLE DATE FILTERS* * Only available if Excel recognizes DATES in the row or column area of the pivot table A N G E L I N A T E N E V A 24

25. Adjusting PT default settings A N G E L I N A T E N E V A PT.RowAxisLayout xlCompactRow  changes pivot table layout 'PT.RowAxisLayout xlOutlineRow 'PT.RowAxisLayout xlTabularRow 25  If allow multiple filters is not set to True, trying to apply multiple filters over one and the same field will overwrite the previously applied ones

26. selecting pivot tables (1) PT.PivotSelect "", xlDataOnly, TruePT.PivotSelect "", xlDataAndLabel, True PT.PivotFields(“Quarter").PivotItems(“ Quarter 2").Caption = “Q2” A N G E L I N A T E N E V A 26

27. PT.RowRange.Select selecting pivot tables (2) PT.DataBodyRange.Select PT.PivotFields("Purpose").PivotItems ("Business").DataRange.Select A N G E L I N A T E N E V A 27

28. selecting pivot tables (3) PT.PivotFields("Quarter").PivotItems ("Quarter 2").DataRange.Select PT.PivotFields("Year").PivotItems("2006"). DataRange.Select A N G E L I N A T E N E V A 28

29. copying pivot tables P A R T I A L LY W H O L E A N G E L I N A T E N E V A 29

30. Find the last row in a pivot table and copy it on another tab A N G E L I N A T E N E V A 30 ‘useful if you just need to consolidate the total numbers of a weekly update in a YTD Summary for example

31. Quickly apply to all (relevant) worksheets Bored of all the hokey-pokey for all those sheets ?31 Angelina Teneva

32. Protecting every sheet in a workbook Angelina Teneva

33. Hiding and Un-hiding & worksheets Angelina Teneva

34. Beautifying Workbooks Hiding Gridlines & Zooming in/out Expanding/Collapsing Grouped Columns

35. Thank you! • Do DECLARE variables • Do write FRAGMENTED Code (i.e. multiple subs for various bits) • Do leave COMMENTS in code, explaining why you do things Angelina Teneva35

Add a comment

Related pages


WORKSHOP EXCELLING YOUR EXCEL 2015. Date : 5 December 2015 Day : Saturday Location : Seminar Room, UiTM Kota Bharu. Terima kasih tak terhingga kepada semua ...
Read more

Excelling in Excel: Advanced (WEBINAR) - YouTube

Excelling in Excel: Advanced (WEBINAR) ... Excelling in Excel: Basics ... Workshop: Coding VBA Macros ...
Read more

Excelling in Excel - cfg.org.uk

Excelling in Excel . What we will look at Filters and subtotal formulas Group and outline Conditional formatting Unique records IF (a reminder)
Read more

Canadian Evaluation Society - New Brunswick Chapter

Survey Research Workshop; Excelling in Excel Workshop (May 31, 2013) ... Excelling in Excel. Presented by Canadian Evaluation Society (CES) ...
Read more

Maheshwar Rao | LinkedIn

View Maheshwar Rao’s professional profile on LinkedIn. ... Internal workshop - Excelling in Excel Internal workshop -Self Management Internal workshop ...
Read more

OSALL NEWSLETTER Librarianship in South Africa

workshop “Excelling in Excel” on Microsoft Excel. Michael speaker at this workshop and gave some ... The newsletter has increased in size and has been most
Read more

Excel | LinkedIn

View 173577 Excel posts, presentations, experts, and more. Get the professional knowledge you need on LinkedIn.
Read more

Find a course | Sydney TAFE - 1300 360 601

Look for your course under your goal career. Contact us by phone 1300 360 601 or enquire online. Got questions? Need help? 1300 360 601. Make an enquiry.
Read more