Excel VBA Code to Copy, Paste Special Values

Many of us use formulas to pull data into a specific worksheet, and once we have the data we want to remove the formulas and just keep the data. You could Copy your selection, Right-click, chose Paste Special, select Values, click OK, and then hit Enter, or Esc, to achieve the desired results. Or you could use the code below and assign it to a button to perform all six steps above in one click:

Sub CopyPasteSpecVal()
'
' CopyPasteSpecVal Macro
' This sub will copy & paste 'Special Values' for the selected region, and then unselect the region from the clipboard.
' Written by Frank Heslin 8/25/08
'

'
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

End Sub

Automate It Then Celebrate It!

Frank Heslin

Comments & Questions welcome and encouraged!

Excel VBA Code for Headers & Footers

Here is the code to add Headers & Footers to your excel worksheets with just one click:

Sub HeaderandFooter()
'
' HeaderandFooter Macro
' This sub will add "Prepared by" and the "Date & Time" in the Header
' and the "Filename & Path" and # of pages in the Footer.
' Written by Frank Heslin 8/25/08


'
With ActiveSheet.PageSetup
.LeftHeader = "&8Prepared by: " & Application.UserName
.RightHeader = "&8Last Printed &D &T"
.LeftFooter = "&8&Z&F &A"
.RightFooter = "&8&P of &N"
.LeftMargin = Application.InchesToPoints(0.4)
.RightMargin = Application.InchesToPoints(0.51)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.Orientation = xlPortrait ' for landscape replace xlPortrait with xlLandscape

End With
ActiveWindow.SelectedSheets.PrintPreview

End Sub ' H&F

Paste this code into a module, and either add the macro to the QAT (2007) or add a button and assign the macro (2003).

How to Learn MS Excel

If you are like most people who begin using a new software application, you probably had a specific task that you were responsible for, and someone showed you how to navigate just enough to complete your task. I know that is how I started using MS Excel back in the mid nineties. If you've used this method then you know that it will not be too long before you start to feel restricted. When this happened to me I began to seek out a method to learn more.

Here is a list of methods that I have used:

• Instructor-led classroom training
• Excel books
• MS Excel Help Menu
• Online forums
• Blogs
• Specific MS Excel menu item help feature (usually includes examples)

My recommendation for those who are brand new to Excel would be to take an instructor-led, beginner's-level class. The most important take-away from this class should be how to navigate to find what you are looking for. Beyond this you will learn the "what" & "how" of the most used features of Excel.

If you are comfortable with navigating new software then the above method will not be of great benefit to you. By using a combination of the other methods listed above you should become comfortable with some of the intermediate functionality rather quickly.

The most important factor in determining your level of success is your attitude. If you enter into your Excel, or any new software training with the approach that “You are going to learn this”, then you certainly will succeed. However, if you begin with an attitude that you “have” to learn this, then you will inevitably struggle. Another very important aspect to consider is which learning method works best for you. For instance, if you thrive on the structure of instructor-led classroom training, then look for classes with an instructor who actually enjoys teaching, and not going through the motions. Prior student comments should clue you in to the instructors’ involvement.

If you prefer using a self-teaching method, as I do, then a combination of all of the above methods can be utilized. For instance, when I began learning Excel I took an instructor-led beginner class. Once I was comfortable with navigation and the basic functionality, I started to go through every menu item by item to learn what it did, and tested each item out to see what it would actually do on a live spreadsheet as opposed to just seeing a static example. I also began searching for online forums since I figured if I have a question about this, odds are that someone else has the same question. I then bought a few Excel books, and started my reference library that has since grown to over two dozen books on various software applications, including updated versions of some of them. I frequently refer to almost all of them for one thing or another. I can usually find an example of what I am looking for , or something that is similar enough that I can modify it for my specific need.

Welcome to AutomateThenCelebrate!

Talking about automating biz processes!
Excel
Word
PowerPoint
Visio
Outlook
Access
VBA
Scripting/Macros
Scheduled Processes
Scheduling
Process Improvement
Streamlining