TheSmallman.com

an XL ideas Lab

  • Home
    • Excel Dashboard Course
    • Financial Modelling Course
    • Excel Infographics Course
    • Excel VBA Course
  • Models
  • Native Excel
  • Charts
  • Dashboards
  • Infographics
  • Modelling
  • VBA
  • Blog
  • About
Menu

Excel Dashboards VBA and more

Street Address
City, State, Zip
Phone Number
an XL ideas Lab

Your Custom Text Here

Excel Dashboards VBA and more

  • Home
  • Courses
    • Excel Dashboard Course
    • Financial Modelling Course
    • Excel Infographics Course
    • Excel VBA Course
  • Models
  • Native Excel
  • Charts
  • Dashboards
  • Infographics
  • Modelling
  • VBA
  • Blog
  • About

Copy/Paste Top n Visible Rows

November 27, 2016 Marcus Small

I recently had a question regarding copying data after a filter had been applied. What if we wanted the top n items from a list to be copied to another place. The problem to address here is that a filtered list can have cells which are hundreds or thousands of rows apart. So copying sequentially might not work. Also, you may not want to Filter by top n. So selecting the top n visible cells in a filtered list becomes a bit problematic. I suppose the simple solution is to copy that filtered list to a new location - only the filtered items will appear and they will be sequential. The following procedure counts the rows from 1 to 10 in a filtered list and copies the results and places them on Sheet 2 in cells A2.

Sub TopNRows()
Dim i As Long
Dim r As Range
Dim rWC As Range

Set r = Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(12)

For Each rwC In r
    i = i + 1
    If i = 10 Or i = r.Count Then Exit For
Next rwC
Range(r(1), rwC).Resize(, 12).SpecialCells(12).Copy sheet2.[A2]
End Sub

The procedure runs on the active sheet and will paste data to the worksheet code name (sheet2). Be sure to check that you actually have a sheet 2 in the VBA editor if you are going to use this procedure. The attached file should help crystalise the procedure. Also the procedure is for the first 12 columns (resize, 12) is to create a larger area to copy.

Enjoy

TopnRecords.xls

← Compare Two Lists on Different Sheets and Output Matches Dependent and Non Dependent Comboboxes →

Featured Posts

Evaluate and Filter Part 2

Evaluate and Filter Part 2
April 26, 2015

 Showing Trends with a Chart

Showing Trends with a Chart
March 5, 2015

Excel VBA to Sort an Array

Excel VBA to Sort an Array
February 23, 2015

 Excel VBA Slicer Selection

Excel VBA Slicer Selection
January 15, 2015

Recent Posts

Protect a Worksheet, Use the Autofilter

Protect a Worksheet, Use the Autofilter October 18, 2018

Countifs Across Sheets

Countifs Across Sheets October 10, 2018

Excel VBA to Select Sheets

Excel VBA to Select Sheets September 22, 2018

Power BI Desktop Dashboard

Power BI Desktop Dashboard August 20, 2018

New Excel Dashboard Tools

New Excel Dashboard Tools August 1, 2018

Watch This Space

Watch This Space July 11, 2018

The Fuzzy World of Financial Modelling

The Fuzzy World of Financial Modelling May 31, 2018

Excel Autofill - Fill Down to Bottom of Range

Excel Autofill - Fill Down to Bottom of Range April 8, 2018

Social Media Dashboard in Excel

Social Media Dashboard in Excel April 5, 2018

Range Areas in Excel with VBA

Range Areas in Excel with VBA March 15, 2018

 

TrainiNg

  • Dashboarding with Excel
  • Visual Analytics
  • Advanced Dashboard Design in Excel
  • Financial Modelling in Excel
  • Advanced Financial Modelling in Excel

 

  • 3 Statement Modelling in Excel
  • Project Financial Modelling
  • PowerPivot
  • Excel Automation with VBA
  • Financial Modelling with VBA

Follow US:

 

marcussmall@thesmallman.com
 

TheSmallman.com - Making your small systems hum...
© Copyright theSmallman.com All Rights Reserved.