101 Ready-To-Use Excel Macros

When working with information in Excel, you often have to transform the data in some way. Transforming it generally means cleaning, standardizing, or shaping data in ways that are appropriate for your work. This can mean anything from cleaning out extra spaces, to padding numbers with zeros, to filtering data for certain criteria.

The macros in this Part shows you some of the more useful macros you can use to dynamically transform the data in your workbooks. If you like, you can combine these macros into one, running each piece of code in a sequence that essentially automates the scrubbing and shaping of your data.

The code for this Part can be found on this book's companion website. See this book's Introduction for more on the companion website.

Macro 45: Copy and Paste a Range

One of the basic data manipulation skills you'll need to learn is copying and pasting a range of data. It's fairly easy to do this manually. Luckily, it's just as easy to copy and paste via VBA.

How it works

In this macro, we use the Copy method of the Range object to copy data from D6:D17 and paste to L6:L17. Note the use of the Destination argument. This argument tells Excel where to paste the data.

Sub Macro45a()

Sheets(“Sheet1”).Range(“D6:D17”).Copy _

Destination:=Sheets(“Sheet1”).Range(“L6:L17”)

End Sub

When working with your spreadsheet, you likely often have to copy formulas and paste them as ...