copy filtered row n paste in another workshee

August 26, 2010 at 00:11:37
Specs: Windows XP
i have workbook which contains sheet1 named "Reference" i want to filter column C and then copy that filtered row and paste that row in another worksheet which name is same as filter condition .

columnB columnC
1 2073 -Regenerant Benzene
2 72 -Depetanizer O/H
3 Benzene Feed From TK
4 Benzene Col. O/H

same type of data upto 30 rows i want to filter column C (like 2073 -Regenerant Benzene) and copy that row and paste in another sheet which name is same as filter condition (2073 -Regenerant Benzene).

Please help me on this

See More: copy filtered row n paste in another workshee

Report •

August 28, 2010 at 06:32:51

You say i want to filter column C and then copy that filtered row.

Usually when you apply a filter it is so that you can see several rows which all contain the selected filter.

If you apply a filter "72 -Depetanizer O/H" then all rows containing "72 -Depetanizer O/H" will be shown.

1. Do your 30 or so rows contain duplicates of the description in different rows in column C, or is there only one instance of each description in the 30 rows
2. Is there more than one worksheet containing this type of data
3. Does your data ever change - if you copy a row to a new worksheet (tab) will you ever need to update that data
4. If there is more than one row with each description, or there is more than one worksheets with data that contains the same description, are the rows just copied to the next available (empty) row on the named worksheet (tab).


Report •

August 30, 2010 at 01:15:00
ans1: ya rows contain duplicates of the description in different rows in column C.

ans2: its 250 worksheet.

ans3: no data will not be change i want to just separate this data by column C content then i will have use that new separated data for stddev and other calculation like average max min.

ans4: in same worksheet rows are contains duplicates in column C and other sheet contains same variable in column c . and yes i just want to copy raw to next empty raws.

Thanks in advance

Report •

August 31, 2010 at 05:51:11

As your requirement is for a one-time process to move data, I have left the macro rather more basic than usual.

I have assumed that the source workbook is in Excel 97/2003 xls format and that none of the worksheets (source or newly created destination) will have data beyond row 65535.

I suggest that you right-click the name tab of one of the worksheets containing your source data and select 'View Code' and paste the code into the large visual basic code window.

You can run it by placing the cursor on the name of the subroutine or anywhere within the subroutine code and clicking the f5 function key.

You should test this on a copy of your data, as changes made by macros cannot be undone with the undo function.

Some of the names you included in your post cannot be used as names of worksheets as they contain characters not permitted in worksheet names. As a result I have included code to remove the most likely non-permitted characters and replace them with underscores.
"72 -Depetanizer O/H" will go into a tab named "72 -Depetanizer O_H"

Here is the code:

Sub SortToSheets()
'code to be used only once - so no error checks included
'code as basic as possible
Dim ws As Worksheet
Dim rngCell As Range

On Error Resume Next

'loop through worksheets
For Each ws In ActiveWorkbook.Worksheets()
    'loop through rows based on content in column C
    'assume Excel 2003 or earlier
    For Each rngCell In ws.Range("C1", Range("C65535").End(xlUp).Address)
        'get content in col. C
        strName = rngCell.Text
        'as some characters aren't permitted as worksheet names
        'make some substitutions
        strName = Replace(strName, "/", "_")
        strName = Replace(strName, "\", "_")
        strName = Replace(strName, "*", "_")
        strName = Replace(strName, "[", "_")
        strName = Replace(strName, "]", "_")
        'test if a worksheet of that name exists
        'if no worksheet - creates error & resumes next
        If Worksheets(strName) Is Nothing Then
            'worksheet doesn't exist, so create it
            Worksheets.Add After:=Worksheets(Worksheets.Count)
            Worksheets(Worksheets.Count).Name = strName
            'copy row to new worksheet
            Worksheets(strName).Range("A1").PasteSpecial Paste:=xlPasteAll
            'worksheet exists, so copy row to it
            Worksheets(strName).Range("A65535").End(xlUp) _
                        .Offset(1, 0).PasteSpecial Paste:=xlPasteAll
        End If
    Next rngCell
Next ws
End Sub

Let me know if this works.


Report •

Related Solutions

September 1, 2010 at 03:26:48
thanks humar its works ur the great great awesom u saved my lots of time.

u great great great great

Report •

September 1, 2010 at 04:07:09
You're very welcome.



Report •

October 7, 2010 at 22:30:16

i have one querry this code is not working properly. for the first sheet i m adding it is working and it is creating sheets and copy data to respective sheet but for the second sheet it is not working it is copying same data as first sheet not new sheet so please look into it sorry for let reply but i was on leave for my i was not able to checkd it properly thanks for your help.


Report •

October 20, 2010 at 01:29:41
please help me on this issue

Report •

Ask Question