Muutke küpsiste eelistusi

E-raamat: 101 Ready-to-use Excel Macros [Wiley Online]

  • Formaat: 336 pages
  • Sari: Mr. Spreadsheet's Bookshelf
  • Ilmumisaeg: 13-Jun-2012
  • Kirjastus: John Wiley & Sons Inc
  • ISBN-10: 1118983920
  • ISBN-13: 9781118983928
Teised raamatud teemal:
  • Wiley Online
  • Hind: 42,28 €*
  • * hind, mis tagab piiramatu üheaegsete kasutajate arvuga ligipääsu piiramatuks ajaks
101 Ready-to-use Excel MacrosSuurem pilt
  • Formaat: 336 pages
  • Sari: Mr. Spreadsheet's Bookshelf
  • Ilmumisaeg: 13-Jun-2012
  • Kirjastus: John Wiley & Sons Inc
  • ISBN-10: 1118983920
  • ISBN-13: 9781118983928
Teised raamatud teemal:
Wiley Online e-raamatudRohkem infot Wiley Online kohta
Raamatu kodulehekülg: https://onlinelibrary.wiley.com/doi/book/10.1002/9781118983928
Reveals over one hundred Excel macros, instructions for creating and customizing VBA macros, and tips for integrating Excel with other Microsoft Office applications.

Save time and be more productive with this helpful guide to Excel macros!

While most books about Excel macros offer only minor examples, usually aimed at illustrating a particular topic, this invaluable resource provides you with the tools needed to efficiently and effectively program Excel macros immediately. Step-by-step instructions show you how to create VBA macros and explain how to customize your applications to look and work exactly as you want them to. By the end of the book, you will understand how each featured macro works, be able to reuse the macros included in the book and online, and modify the macro for personal use.

  • Shows you how to solve common problems with the featured macros, even if you lack extensive programming knowledge
  • Outlines a problem that needs to be solved and provides the actual Excel macro, as well as the downloadable code, to solve the problem
  • Provides an explanation of how each macro works and where to use the macro

With 101 Ready-to-Use Excel Macros, Microsoft MVP Michael Alexander helps you save time, automate tasks, and ultimately be more productive.

Introduction 1(4)
Topics Covered
1(1)
What You Need to Know
2(1)
What You Need to Have
2(1)
How This Book Is Organized
2(2)
Part I Getting Started with Excel Macros
3(1)
Part II Working with Workbooks
3(1)
Part III Automating Worksheet Tasks with Macros
3(1)
Part IV Selecting and Modifying Ranges
3(1)
Part V Working with Data
3(1)
Part VI Working with PivotTables
3(1)
Part VII Manipulating Charts with Macros
3(1)
Part VIII E-Mailing from Excel
4(1)
Part IX Integrating Excel and Other Office Applications
4(1)
Conventions in This Book
4(1)
What the icons mean
4(1)
About the Companion Website
4(1)
Part I Getting Started with Excel Macros
5(32)
Becoming Familiar with Macro Recording Basics
7(4)
Comparing Absolute and Relative Macro Recording
11(4)
Recording macros with absolute references
11(2)
Recording macros with relative references
13(2)
Looking at Other Macro Recording Concepts
15(4)
Macro-enabled file extensions
15(1)
Macro security in Excel 2010
16(1)
Trusted locations
16(1)
Storing macros in your Personal Macro Workbook
17(1)
Assigning a macro to a button and other form controls
17(2)
Placing a macro on the Quick Access toolbar
19(1)
Working In the Visual Basic Editor
19(2)
Activating the VBE
19(1)
Understanding VBE components
19(2)
Working with the Project window
21(3)
Adding a new VBA module
22(1)
Removing a VBA module
23(1)
Working with a Code Window
24(3)
Minimizing and maximizing windows
24(1)
Getting VBA code into a module
25(2)
Customizing the VBA Environment
27(4)
The Editor tab
27(2)
The Editor Format tab
29(1)
The General tab
30(1)
The Docking tab
31(1)
Discovering the Excel Object Model
31(3)
Understanding objects
31(1)
Understanding collections
32(1)
Understanding properties
33(1)
Understanding methods
33(1)
Taking a Brief Look at Variables
34(1)
About the Macros in This Book
35(2)
Getting the sample files
35(1)
Using the sample files
35(1)
Things to keep in mind
36(1)
Part II Working with Workbooks
37(34)
Macro 1 Creating a New Workbook from Scratch
39(2)
How it works
39(1)
How to use it
40(1)
Macro 2 Saving a Workbook when a Particular Cell Is Changed
41(2)
How it works
41(1)
How to use it
42(1)
Macro 3 Saving a Workbook before Closing
43(3)
How it works
43(1)
How to use it
44(2)
Macro 4 Protect a Worksheet on Workbook Close
46(2)
How it works
46(1)
How to use it
46(2)
Macro 5 Unprotect a Worksheet on Workbook Open
48(2)
How it works
48(1)
How to use it
48(2)
Macro 6 Open a Workbook to a Specific Tab
50(2)
How it works
50(1)
How to use it
50(2)
Macro 7 Opening a Specific Workbook Defined by the User
52(2)
How it works
52(1)
How to use it
53(1)
Macro 8 Determine Whether a Workbook Is Already Open
54(3)
How it works
54(2)
How to use it
56(1)
Macro 9 Determine Whether a Workbook Exists in a Directory
57(2)
How it works
57(1)
How to use it
58(1)
Macro 10 Refresh All Data Connections in Workbook on Open
59(2)
How it works
59(1)
How to use it
59(2)
Macro 11 Close all Workbooks at Once
61(2)
How it works
61(1)
How to use it
61(2)
Macro 12 Open All Workbooks in a Directory
63(2)
How it works
63(1)
How to use it
64(1)
Macro 13 Print all Workbooks in a Directory
65(2)
How it works
65(1)
How to use it
66(1)
Macro 14 Preventing the Workbook from Closing until a Cell Is Populated
67(2)
How it works
67(1)
How to use it
68(1)
Macro 15 Create a Backup of a Current Workbook with Today's Date
69(2)
How it works
69(1)
How to use it
70(1)
Part III Automating Worksheet Tasks with Macros
71(32)
Macro 16 Add and Name a New Worksheet
73(2)
How it works
73(1)
How to use it
74(1)
Macro 17 Delete All but the Active Worksheet
75(2)
How it works
75(1)
How to use it
76(1)
Macro 18 Hide All but the Active Worksheet
77(2)
How it works
77(1)
How to use it
78(1)
Macro 19 Unhide All Worksheets in a Workbook
79(2)
How it works
79(1)
How to use it
79(2)
Macro 20 Moving Worksheets Around
81(2)
How it works
81(1)
How to use it
81(2)
Macro 21 Sort Worksheets by Name
83(2)
How it works
83(1)
How to use it
84(1)
Macro 22 Group Worksheets by Color
85(2)
How it works
85(1)
How to use it
86(1)
Macro 23 Copy a Worksheet to a New Workbook
87(1)
How it works
87(1)
How to use it
87(1)
Macro 24 Create a New Workbook for Each Worksheet
88(2)
How it works
88(1)
How to use it
89(1)
Macro 25 Print Specified Worksheets
90(2)
How it works
90(1)
How to use it
90(2)
Macro 26 Protect All Worksheets
92(2)
How it works
92(1)
How to use it
92(2)
Macro 27 Unprotect All Worksheets
94(2)
How it works
94(1)
How to use it
95(1)
Macro 28 Create a Table of Contents for Your Worksheets
96(3)
How it works
96(2)
How to use it
98(1)
Macro 29 Zooming In and Out of a Worksheet with Double-Click
99(2)
How it works
99(1)
How to use it
100(1)
Macro 30 Highlight the Active Row and Column
101(2)
How it works
101(1)
How to use it
102(1)
Part IV Selecting and Modifying Ranges
103(34)
Macro 31 Selecting and Formatting a Range
105(3)
How it works
105(2)
How to use it
107(1)
Macro 32 Creating and Selecting Named Ranges
108(3)
How it works
109(1)
How to use it
110(1)
Macro 33 Enumerating Through a Range of Cells
111(2)
How it works
111(1)
How to use it
112(1)
Macro 34 Select and Format All Named Ranges
113(2)
How it works
113(1)
How to use it
114(1)
Macro 35 Inserting blank Rows in a Range
115(2)
How it works
115(1)
How to use it
116(1)
Macro 36 Unhide All Rows and Columns
117(1)
How it works
117(1)
How to use it
117(1)
Macro 37 Deleting blank Rows
118(2)
How it works
118(1)
How to use it
119(1)
Macro 38 Deleting blank Columns
120(2)
How it works
120(1)
How to use it
121(1)
Macro 39 Select and Format All Formulas in a Workbook
122(3)
How it works
122(1)
How to use it
123(2)
Macro 40 Find and Select the First blank Row or Column
125(3)
How it works
125(2)
How to use it
127(1)
Macro 41 Apply Alternate Color Banding
128(2)
How it works
128(1)
How to use it
129(1)
Macro 42 Sort a Range on Double-Click
130(2)
How it works
130(1)
How to use it
131(1)
Macro 43 Limit Range Movement to a Particular Area
132(2)
How it works
132(1)
How to use it
132(2)
Macro 44 Dynamically Set the Print Area of a Worksheet
134(3)
How it works
134(1)
How to use it
134(3)
Part V Working with Data
137(50)
Macro 45 Copy and Paste a Range
139(2)
How it works
139(1)
How to use it
140(1)
Macro 46 Convert All Formulas in a Range to Values
141(2)
How it works
141(1)
How to use it
142(1)
Macro 47 Perform the Text to Columns Command on All Columns
143(4)
How it works
144(2)
How to use it
146(1)
Macro 48 Convert Trailing Minus Signs
147(3)
How it works
147(2)
How to use it
149(1)
Macro 49 Trim Spaces from All Cells in a Range
150(2)
How it works
150(1)
How to use it
151(1)
Macro 50 Truncate ZIP Codes to the Left Five
152(3)
How it works
152(2)
How to use it
154(1)
Macro 51 Padding Cells with Zeros
155(3)
How it works
155(2)
How to use it
157(1)
Macro 52 Replace blanks Cells with a Value
158(3)
How it works
158(2)
How to use it
160(1)
Macro 53 Append Text to the Left or Right of Your Cells
161(2)
How it works
161(1)
How to use it
162(1)
Macro 54 Create a Super Data Cleanup Macro
163(3)
How it works
163(2)
How to use it
165(1)
Macro 55 Clean Up Non-Printing Characters
166(2)
How it works
166(1)
How to use it
167(1)
Macro 56 Highlight Duplicates in a Range of Data
168(2)
How it works
168(1)
How to use it
169(1)
Macro 57 Hide All Rows but Those Containing Duplicate Data
170(2)
How it works
170(1)
How to use it
171(1)
Macro 58 Selectively Hide AutoFilter Drop-down Arrows
172(3)
How it works
173(1)
How to use it
174(1)
Macro 59 Copy Filtered Rows to a New Workbook
175(2)
How it works
175(1)
How to use it
176(1)
Macro 60 Create a New Sheet for Each Item in an AutoFilter
177(6)
How it works
177(5)
How to use it
182(1)
Macro 61 Show Filtered Columns in the Status Bar
183(4)
How it works
183(2)
How to use it
185(2)
Part VI Working with PivotTables
187(46)
Macro 62 Create a Backwards-Compatible PivotTable
189(3)
How it works
190(1)
How to use it
191(1)
Macro 63 Refresh All PivotTables Workbook
192(2)
How it works
192(1)
How to use it
193(1)
Macro 64 Create a PivotTable Inventory Summary
194(3)
How it works
194(2)
How to use it
196(1)
Macro 65 Make All PivotTables Use the Same Pivot Cache
197(2)
How it works
197(1)
How to use it
198(1)
Macro 66 Hide All Subtotals in a PivotTable
199(3)
How it works
199(2)
How to use it
201(1)
Macro 67 Adjust All Pivot Data Field Titles
202(2)
How it works
202(1)
How to use it
203(1)
Macro 68 Set All Data Items to Sum
204(3)
How it works
204(2)
How to use it
206(1)
Macro 69 Apply Number Formatting for All Data Items
207(4)
How it works
207(9)
How to use it
216
Macro 70 Sort All Fields in Alphabetical Order
211(2)
How it works
211(1)
How to use it
212(1)
Macro 71 Apply Custom Sort to Data Items
213(2)
How it works
213(1)
How to use it
214(1)
Macro 72 Apply PivotTable Restrictions
215(2)
How it works
215(1)
How to use it
216(1)
Macro 73 Apply Pivot Field Restrictions
217(2)
How it works
217(1)
How to use it
218(1)
Macro 74 Automatically Delete Pivot Table Drill-Down Sheets
219(4)
How it works
219(2)
How to use it
221(2)
Macro 75 Print Pivot Table for Each Report Filter Item
223(3)
How it works
223(2)
How to use it
225(1)
Macro 76 Create New Workbook for Each Report Filter Item
226(3)
How it works
226(2)
How to use it
228(1)
Macro 77 Transpose Entire Data Range with a PivotTable
229(4)
How it works
230(1)
How to use it
231(2)
Part VII Manipulating Charts with Macros
233(20)
Macro 78 Resize All Charts on a Worksheet
235(2)
How it works
235(1)
How to use it
236(1)
Macro 79 Align a Chart to a Specific Range
237(2)
How it works
237(1)
How to use it
238(1)
Macro 80 Create a Set of Disconnected Charts
239(2)
How it works
239(1)
How to use it
240(1)
Macro 81 Print All Charts on a Worksheet
241(2)
How it works
241(1)
How to use it
242(1)
Macro 82 Label First and Last Chart Points
243(3)
How it works
243(2)
How to use it
245(1)
Macro 83 Color Chart Series to Match Source Cell Colors
246(4)
How it works
246(3)
How to use it
249(1)
Macro 84 Color Chart Data Points to Match Source Cell Colors
250(3)
How it works
250(2)
How to use it
252(1)
Part VIII E-Mailing from Excel
253(20)
Macro 85 Mailing the Active Workbook as an Attachment
255(3)
How it works
255(2)
How to use it
257(1)
Macro 86 Mailing a Specific Range as Attachment
258(2)
How it works
258(1)
How to use it
259(1)
Macro 87 Mailing a Single Sheet as an Attachment
260(2)
How it works
260(1)
How to use it
261(1)
Macro 88 Send Mail with a Link to Our Workbook
262(2)
How it works
262(1)
How to use it
263(1)
Macro 89 Mailing All E-Mail Addresses in Our Contact List
264(2)
How it works
264(1)
How to use it
265(1)
Macro 90 Saving All Attachments to a Folder
266(3)
How it works
266(2)
How to use it
268(1)
Macro 91 Saving Certain Attachments to a Folder
269(4)
How it works
269(2)
How to use it
271(2)
Part IX Integrating Excel and Other Office Applications
273(29)
Macro 92 Running an Access Query from Excel
275(3)
How it works
275(2)
How to use it
277(1)
Macro 93 Running an Access Macro from Excel
278(2)
How it works
278(1)
How to use it
279(1)
Macro 94 Opening an Access Report from Excel
280(2)
How it works
280(1)
How to use it
281(1)
Macro 95 Opening an Access Form from Excel
282(2)
How it works
282(1)
How to use it
283(1)
Macro 96 Compacting an Access Database from Excel
284(2)
How it works
284(1)
How to use it
285(1)
Macro 97 Sending Excel Data to a Word Document
286(3)
How it works
286(2)
How to use it
288(1)
Macro 98 Simulating Mail Merge with a Word Document
289(4)
How it works
289(3)
How to use it
292(1)
Macro 99 Sending Excel Data to a PowerPoint Presentation
293(3)
How it works
293(2)
How to use it
295(1)
Macro 100 Sending All Excel Charts to a PowerPoint Presentation
296(3)
How it works
296(2)
How to use it
298(1)
Macro 101 Convert a Workbook into a PowerPoint Presentation
299(3)
How it works
299(2)
How to use it
301(1)
Index 302
Mike Alexander is a Microsoft Certified Application Developer (MCAD) and author of several books on advanced business analysis with Microsoft Access and Excel. He has more than 15 years of experience consulting and developing Office solutions. Michael has been named a Microsoft MVP for his ongoing contributions to the Excel community. In his spare time, he runs a free tutorial site, www.datapigtechnologies.com, where he shares Excel and Access tips. John Walkenbach is author of more than 50 spreadsheet books and lives in southern Arizona. Visit his website at http://spreadsheetpage.com.
Püsilink: https://www.kriso.ee/db/9781118983928_pe.html
Märksõnad: