Streamlining Macro Created by Macros Recorder

Sponsor

Excel Tips Streamlining Macro Created by Macros Recorder [Q]

I used the macro recorder in Excel to create a nice macro to reorganize some raw data into a format more useful for Excel to work with. In moving the data around, at one point I selected a range (J8:R8) and copied/pasted the formulas in it down to the bottom of the worksheet, then copied that entire range and pasted values. That portion of the code (from the recorder) looked like this:

Range(”J8:R8″).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks :=False, Transpose:=False

I’d really like to knock this down to just a couple of lines, maybe three at
most. Can anyone help out? Thanks.

Excel Tips Streamlining Macro Created by Macros Recorder [A]

Your “three” line excel tips is below, along with a separate approach. (You’ll
likely get a number of answers that all have a slightly different way to
achieve your results).

Sub ThreeLines()
Range(”J8:R8″).Copy Range(Range(”J8:R8″), Range(”J8:R8″).End(xlDown))
Range(Range(”J8:R8″), Range(”J8:R8″).End(xlDown)).Copy
Range(Range(”J8:R8″), Range(”J8:R8″).End(xlDown)).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

Sub SeparateApproach()
Dim Rng As Range
Set Rng = Range(”J8:R8″)
Set Rng = Range(Rng, Rng.End(xlDown))
Rng.FillDown
Rng.Copy
Rng.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

Rate This Tips:

Incoming excel search terms

,how to write macro recorder for excel,macro recorder tips,vba macro transferring data between workbooks,excel created by,vba transpose copy,copy paste excel macro transfer workbook,data streamlining in excel,excel filldown selection macro,excel formula selection copy,excel how to limit filldown,excel macro copy and paste to another worksheet by criteria,excel macro copy data between workbooks,excel macro copy range image,excel macro excel xldown,excel macro fill down,excel macro if formula,excel macro recoder limit,excel macro recorder tips,excel macro recording tips,excel macro to copy multiple worksheets workbooks,excel macro trendline,excel macro tricks,excel transpose macro in different workbooks,excel vba code streamlining sheet change,excel vba copy between spreadsheets,excel vba copy paste tricks,excel vba paste transpose,excel vba sending data between macros,excel vba tips and tricks,excel vba xldown,excel vba xlpastevalues,fill down excel macro,formulas excel macro recorder,how are macros created,how does excel macro recorder determine cell range,how to specify filldown limit in excel macros,how to transpose date from one workbook to another macro,macro copy range down,macro for transposing data excel,macro recorder goes to each worsheet,macro recorder to transfer data from a row to another worksheet excel,macro to paste range,macros for excel to copy all active worksheets and copy and paste to a new sheet,microsoft excel vba selection copy,paste to another worksheet excel,pastespecial paste:=xlpastevalues,rng recorder,streamline excel macro,streamlining excel macros

Related Excel Tips

• How To Transpose Column into Rows (0)
• How to Divide a Number With “Less Than” Sign? (1)
• Transfer Data Between Workbooks (0)
• Sending Multiple Personalized Email from Excel (3)
• Resize Range for Column in Workbook (1)
• Problem When Inserting Image or Picture Box (0)
• How to Draw Lines on User Form (0)

Comments

• Archives

  • October 2009
  • September 2009
  • August 2009
  • July 2009
  • June 2009
  • May 2009
  • April 2009
  • March 2009

• Categories

  • Excel Vba
  • Excel Tips
  • Excel Formulas
  • Excel Date Functions
  • Excel Spreadsheet
  • Excel Worksheet
  • Excel Miscellaneous

• Partners

  • Search Xls
  • Xls Files

• Latest Excel Tips

  • Search-Engine-Excel.Com - The Helpful One for You
  • Switch Minutes to Seconds in Excel
  • Excel Polynomial Trendline
  • Sending Multiple Personalized Email from Excel
  • Excel Sumif Multiple Criteria
  • Remove Hyperlinks in Excel
  • Copy Entire Rows to Another Spreadsheet Based on Cell Criteria
  • Streamlining Macro Created by Macros Recorder
  • Copying Cells Which Include Conditional Formatting in Worksheet
  • How to Draw Lines on User Form

• Most Viewed Excel Tips

  • How to Divide a Number With “Less Than” Sign? - 14,183 views
  • Date Calculation Start Date Minus Year, Month and Days - 13,944 views
  • Macro VBA to Remove Characters in String of Text - 13,158 views
  • Combine Multiple Workbooks Overcome 255 Char Limit - 11,922 views
  • Copy Entire Rows to Another Spreadsheet Based on Cell Criteria - 11,069 views
  • Protect Range of Rows and Columns in Worksheet with Macro - 11,002 views
  • Insert Introduce Time Delay in Macro Vba - 9,520 views

• Best Excel Tips

  • Calling Macro Visual Basic Procedures of a Different Project (5.00 out of 5)
  • Copy from Worksheet (5.00 out of 5)
  • Change Worksheet Tab Colors (5.00 out of 5)
  • Delete Worksheet (5.00 out of 5)
  • Quick Way Determine The First and Last Business Day of the Month (5.00 out of 5)
  • How to Calculate 6 Working Days Between Two Dates (5.00 out of 5)
  • Date Calculation Start Date Minus Year, Month and Days (5.00 out of 5)

• Excel Tags

  Excel Vba vba excel xcel vba vba xcel Excel Tips Excel Formulas excel formula xcel formulas xcel tips excel vba add worksheet excel vba create new workbook excel worksheet formulas xcel formula Excel Worksheet microsoft excel tips excel vba date functions excel date function Excel Spreadsheet excel vba active worksheet excel vba worksheet change excel vba worksheet name excel vba worksheet_change excel vba delete worksheet excel vba select worksheet excel vba clear worksheet excel vba copy worksheet excel vba macro add new worksheet how to add new worksheet excel miscellanous