Copy Range of Cells from Filtered List

[mar4jc]

Hello there,
I would like to create a macro that copies top 10 results in another spreadsheet. I have sorted the list and have multiple filters activated. Now, I want to copy the first 10 entries in column A of the filtered list and paste them to another sheet. How can I do this in a macro? Any help is greatly appreciated.
Thanks,
marj4c

[kkknie]

Here's a generic solution. Change the values of the variables at the top to suit any worksheet name, copy range, paste range or number of paste values.

Sub MoveEm()

Sheets(1).Select

Dim i As Integer

Dim strCopySheet As String
Dim strPasteSheet As String

Dim iCopyStart As Integer
Dim iCopyCol As Integer
Dim iCopyMax As Integer

Dim iPasteStart As Integer
Dim iPasteCol As Integer
Dim iPasteRow As Integer

strCopySheet = "Sheet1"
strPasteSheet = "Sheet2"

iCopyStart = 2 'Row 2
iCopyCol = 1 'Column A
iCopyMax = 10 'Top 10

iPasteStart = 2 'Row 2
iPasteCol = 1 'Column A
iPasteRow = iPasteStart 'Initially set to start row

Sheets(strCopySheet).Select
With Sheets(strPasteSheet)

For i = 2 To 10000
Debug.Print iPasteRow
If Cells(i, iCopyCol).Rows.Hidden = False Then
.Cells(iPasteRow, iPasteCol).Value = Cells(i, iCopyCol).Value
iPasteRow = iPasteRow + 1
End If
If iPasteRow >= iCopyMax + iPasteStart Then Exit For
Next

End With

End Sub

Good luck,

K

[mar4jc]

Thanks kkknie,
That looks like a very workable solution. Would I be able to pass iCopyStart, iCopyCol, iPasteStart, iPasteCol as arguments for this macro? I'd like to be perform the copy/pasting multiple times from different start and end points. Please advise.
Thanks,
mar4jc

[kkknie]

Sure 'nuff.

Change the first line to:

Sub MoveEm(iCopyStart, iCopyCol, iPasteStart, iPasteCol)

And call one of two ways (I think)

MoveEm 2, 1, 2, 1

or

Call MoveEm(2,1,2,1)

K

[mar4jc]

Thanks K,
Your script worked like a champ. The only thing I had to change was the following line:

For i = iCopyStart To 10000

This set the iCopyStart to the intended start instead of Row 2. Thanks again for your help. My reporting has just gotten a lot easier!
Thanks again,
mar4jc