Free Microsoft Excel 2013 Quick Reference

How do you copy filtered data to a new sheet?

In Microsoft 2000, I could filter data and then copy it to a new worksheet
getting only the data showing on the screen. I am now using Enterprise 2007
and am trying to do the same. Everytime I try this, it copies all the data,
not just the data on the screen that excluded the data I have filtered out.
Are there any tricks for being able to do this as easily in Enterprise 2007
as I did in 2000? Any help would be greatly appreciated. Thanks!

Post your answer or comment

comments powered by Disqus
How do you change the date to a Number, example Jan 1, 2005 is the 1st day of
the year and so on...


I am trying to copy data to a new sheet and having some problems.

I need to copy all passes and faileds to a new sheet and work out the

Column R contains whether the item is a "Pass" or "Fail" and is
automatically filled in by the formula

I have the following code copied from elsewhere on this site, but it does
not work. Returns the message "There are no items to move."!

Also, once complete, how do I assign the macro to a button on the toolbar?

Any help would be gratefully received as it would automate a very laborious


I bid hello the forum!

I have a master list full of the personnel-to-be-fed of our "full day-care":

(1)____ (Teacher)_(Fine Arts)___(John Brown)__(A4)_____(6999999)
(2)____ (Student)_(Literature)___(Jenna Smith)_(C4)_____(6888888)
(3)____ (Student)_(Football)____(Josh Bink)____(D3)_____(6777777)
(4)____ (Nurse)_____(N/A)_____(Jenna Blur)___(N/A)_____(6555555)

I use a "master" worksheet in which all of the personnel is listed sorted by their NAME. I would like to create a VBA button which automatically would distribute the data to a new sheet according to their "RANK" (a new "Teacher"-sheet, "Student"-sheet etc) and sort them by "NAME".

Could someone help me through this?
I would really appreciate it. Thank you in advance

Hello everybody,

I hope you are all well. My question is quite simple. I have a file for my database with 10 sheets, who are similarly built (same columns). I would like to extract filtered data to another sheet who would be a recap.
I would like only to extract the columns A, B , and C (First name, name and another criteria) and the filter is applied on the column B.
I have Excel 2007. I hope that i have been clear, if you really need it i can upload an example (not very easy for me as data is confidential).

All the best,


I am trying to copy entire rows to a new sheet if certain criteria are met in one column. The master worksheet, column A has 500+ names, several columns to the right (after DOB, addresses, etc) every person in column A has one of 10 different counselors assigned to them. I’d like to create a separate worksheet for each counselor that has a list of the names of each person they are assigned to. I’d like for it to be formula driven so that as people in column A of the main worksheet are added or re-assigned that the worksheet for each counselor updates automatically. It’s a similar request to that starting at the below link, but in my case, I already have a worksheet that tabulates numbers. In my situation the counselors need a list of names. Is this possible in Excel?

Is it possible to copy worksheet names to a new sheet without macro. I'm lost with macro or VB, but I have a hope it could be done otherwise as well.


Hi, I'm Carol and I'm brand new to the Forums.

My position requires me to take variable number of samples from existing reports and analyze them for compliance with our standards. I get about 13 different reports each month, and I've gotten a lot of great information from this and other forums (mostly this one) on how to do most of what I need to do, such as: grab a random sample based on how many I need, copy the sample over to a new sheet (creating it if it doesn't already exist), and grabbing the corresponding cells of the sample items for the data points I need to analyze. This forum has been an absolute lifesaver, and I've been able to find most of what I need just by searching.

My problem is automating the last part. Right now, I use the same module on all my reports that

1. asks me how many items I want to pull from the active sheet
2. asks me on what sheet do I want the results and
3. pastes a random number of those unique ID #s from the original sheet to the target sheet with no change in the code.

I also have it pull 3-4 corresponding cells from the original sheet that in the same row as each unique ID # and paste them on the target sheet, but I have to manually tell it which cells to compare and copy, and I have to manually tell it from which cells to get the headers for that data (for example, on one report the Unique ID # is an investigation #, then I have three criteria that I have to analyze "Accuracy" "Completeness" and "Documented") that sits among several other cells with info I don't need to look at and therefore don't need to copy from the original to the target sheet.

So what I want to do is ask "How many headers will you need?"

Take that variable (say it's 3) and then ask

What do you want to name Header 1? And put Header 1 in to Cells (1, 2) on target sheet, then say I name it "Accuracy" and "Accuracy" is part of the header on the original sheet. I then what it to know that it's taking the corresponding data from the the row of unique ID # (call it 12345678) and the same column as the "Accuracy" header on that original sheet and move it to the target sheet.

I then want it to ask "What do you want to name Header 2? and do the same thing with Cells (1, 3) on the target sheet.

Here is my code for the manual part of that task

    Worksheets(targsheet).Cells(1, 1) = Worksheets(origsheet).Cells(5, 5)
    Worksheets(targsheet).Cells(1, 2) = Worksheets(origsheet).Cells(4, 7)
    Worksheets(targsheet).Cells(1, 3) = Worksheets(origsheet).Cells(4, 13)
    Worksheets(targsheet).Cells(1, 4) = Worksheets(origsheet).Cells(4, 17)
    'Worksheets(targsheet).Cells(1, 5) = Worksheets(origsheet).Cells(1, 14)
    'now we populate the target sheet using the row numbers placed into the results array
    For i = 1 To NbrItems
        Worksheets(targsheet).Cells(i + 1, 1) = Worksheets(origsheet).Cells(Results(i), 5)
    Next i
Set sh1 = Worksheets(targsheet)
Set sh2 = Worksheets(origsheet)

lastrow1 = sh1.Cells(Rows.Count, "A").End(xlUp).Row
lastrow2 = sh2.Cells(Rows.Count, "A").End(xlUp).Row

For k = 2 To lastrow1
For h = 2 To lastrow2

If sh1.Cells(k, "A").Value = sh2.Cells(h, "E").Value Then

sh1.Cells(k, "B").Value = sh2.Cells(h, "G").Value
End If
Next h
Next k

For k = 2 To lastrow1
For h = 2 To lastrow2

If sh1.Cells(k, "A").Value = sh2.Cells(h, "E").Value Then

sh1.Cells(k, "C").Value = sh2.Cells(h, "M").Value
End If
Next h
Next k

For k = 2 To lastrow1
For h = 2 To lastrow2

If sh1.Cells(k, "A").Value = sh2.Cells(h, "E").Value Then

sh1.Cells(k, "D").Value = sh2.Cells(h, "Q").Value
End If
Next h
Next k
Any help is appreciated.




Is it possible to copy a specific number of rows from a filter set of records in a worksheet?

For example, say there are 2000 rows in a worksheet and after applying filter to a certain column i get 250 rows. Out of these 250 rows, say i randomly choose 13 rows. Now, I need these rows to be copied to a new sheet. Then, i remove this filter and reapply it. This time i get 100 records and from this i randomly choose 5 rows. Now i want this 5 rows to be appended to the 13 rows chosen previously. Likewise, this process continues till stop with applying the filter. And each time when i apply the filter, certain number of rows are picked (which will be calculated at runtime) based on a specific rate and the number of rows output after filter is applied.

How can i randomly choose certain no. of rows from a set of filtered records in a worksheet?

I'm able to pick out and identify rows that are filtered with the code below, but i need some help to tweak it a bit to suit my requirement.

   Dim rngFilter As Range
   Dim rngVisible As Range
    'Is there an autofilter on Worksheet?
   If Sheet1.AutoFilterMode Then
        'Are any filters being used?
       If Sheet1.FilterMode Then
            'Get a reference to the autofilter range
           With Sheet1.AutoFilter.Range
               Set rngFilter = .Offset(1).Resize(.Rows.Count - 1, .Columns.Count)
           End With
       End If
   End If
   If rngFilter Is Nothing Then
       MsgBox "No Filter has been applied!"
        'Find the visible cells, if there are none then an error will be raised
       On Error Resume Next
       Set rngVisible = rngFilter.SpecialCells(xlCellTypeVisible)
       On Error Goto 0
       If rngVisible Is Nothing Then
           MsgBox "There are no visible cells!"
           MsgBox rngVisible.Address    'Needs tweaking here
       End If
   End If
End Sub

Can some help me with my requirement above?


I work as a teacher and I spend a lot of time analysing the results of the 2000 students in the school which are split over 5 years and across 20 departments.

To do this effectively I copy the students teaching groups into excel (Cols D to X) then I will paste in the students Targets for the 20 subjects (AA-AT) and then their Progress Grades (BA-BT).

One of the problems is that not all students study all the subjects, e.g., In one year group only 40 students out of 330 will study History. So to separate this data off I use Autofilter (non blanks) in the History Teaching Groups column and then Autofilter (Blanks) in the Targets and Progress Grades to check that all students have data.


At the end of each column I have a simple formula which Counts all the As, Bs, Cs, Ds etc. that have been entered and then calculate the percentage of each grade across the year group.


I now want to copy this data (after having used the Hide Cols tool) to a seperate sheet (one for each dept.) but when I do this (it is still in Autofilter) and then use the paste of paste special tool it only pastes in #REF where there was a range or returns a value of 1.

Is there a way copying the data into a new sheet that will keep the ranges and criterias and return the data from the original spreadsheet or is there (although difficult from my desc. an easier way to do this?)

I can email you or post an example of what i mean it it'll make this easier to understand!!!

Any help would be great!


J Banks

A reasonable Excel user! But a VBA novice!

I am using microsoft excel 2007. I had applied filter to my data and when i try to copy that filtered data to a new sheet the whole data gets copied..i just want to copy my filtered data pls help and provide some solution

I have an excel sheet which contains multiple columns and rows, In which I want the all the data including duplicates because it may be with different date. (I also need data related to same date for the mentioned names because time may virie) corresponding to column E with names (Kishore, Srikanth and Prasad) to a new sheet. Please help me in doing it and if it a macro please let me know how to use it.

Attaching the excel file also.


I have a table on a sheet which has an auto filter on it.

I would like to run a macro that will copy the result of a selected filter to a new sheet.

Note that the table is a growing table as new items are added to it.

Any ideas will be great!!


Here is what I would like to do.
I have several work sheets named store1, store2, store3....every sheet has dates in the same cells (C3,D3,E3...)
How can I sort that data on a new work sheet wich would have dates in a column, and next to the dates all the worksheet names (store1,store3...) wich contain the mentioned date?


I am trying to copy entire rows to a new sheet if certain criteria are met in one column "E". This is a workbook for sorting students to classes. There is the main worksheet (All students) and 3 subsequent others, (2nd_1), (2nd_2") and (Summary"). What I am trying to do is if a student is placed in class "2nd_1" --column E of worksheet (All students) then that entire row is copied to the sheet (2nd_1). But I want it to start copying to row 3 of that sheet. Also if the text of "2nd_2" is entered into column E of the main worksheet(All Students) then it is copied to sheet (2nd_2) starting in row 3 again. Is this possible. I have attached my workbook. Any help would be appreciated as I have very little experience with VBA

How do you copy a sheet from one workbook to a different workbook?

Sub Workordercopy()
ActiveSheet.Copy Before:=Workbooks("workorders.xls").Sheets(1)
End Sub

How do you copy an entire row in excel to a new tab based on a cell value. Then delete the cell in the first sheet

I am very new to VBA and need some assistance. I'm trying to set up a set of sheets to track my stock trades. I have one sheet called "2011 Stock Trades" in which I'll input all my trades and it will auto calculate how long I've owned the stock and if it's a long term, or short term trade.

What I'd like is to know if there is some VBA scripting I can utilize to automatically copy entire rows of data from the "2011 Stock Trades" to one of two other sheets (named "ST" for Short Term Trades, and "LT" for Long Term Trades) as data is input based on the text string in Column R. The variable I'd like it based on is when there is text in the R Column that equals "Short Term" it copies the data on that row to the ST Sheet and if it equals "Long Term" it copies that row to the LT Sheet.

I've gone over a few other similar posts but they are things that require clicking a button on each line to copy data. I also am really new to the VBA stuff and couldn't figure out how to edit it to do what I'm looking for. I'm hoping to just have the macro automatically run as data is input on the "2011 Stock Trades" sheet if that's even remotely possible.

I've attached a sample with a few trades (one short, one long) input to this message so you can clearly picture what I'm trying to do. You'll see on the ST & LT sheets that I want the first row of data to copy into row 7 and continue down from there.

Thanks in advance for any help you can offer. I truly appreciate it!


Worksheets(1) row 26 contains Headers
Worksheets(1) row 27 to row 282 contains data
How do I copy each row to a separate worksheet automatically?
Thank you so much for your help!

Worksheets(1) row 26 contains Headers
Worksheets(1) row 27 to row 282 contains data
How do I copy each row to a separate worksheet automatically?
Thank you so much for your help!

How do you printe all the data in a cell. Excel only visibily displays up to
1024 characters but you can have more data than that. How do make excell
print the entire cell data?



I need excel to copy and sum the data från several rows to a single row when I filter it by a name or so.

My columns looks like this:
Occupation, role, hours, name

When I filter by name and role I need all rows containg the name and role to be gathered in a single row.

How is that done?

I know how to sum the filtered rows if they are containing numbers.
But what about the text (that is same for the filtered rows)?

Thanks in advance.

If i have data in one cell and it is formatted and I want to copy the new formatted data to a newcell (without having to use the formats)- How do I do it? Is there a macro?

Ex. 89077890 in a cell. It is formatted as a zip so it reads 08907-7890

I want a macro that will allow me to copy the cell and it paste in 08907-7890. I tried paste formatting and paste special values. Both just copy the original text.

how do you copy hyperlinks from one worksheet to another and change the
hyperlink to reflect the sheet being copied to rather than the original sheet.

How do I copy the contents of a cell from one workbook to another (as a
formula)? I tried '=[workbook name]worksheet name!cell name' but that
doesn't seem to work

No luck finding an answer? You could always try Google.