Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating sheet and copying filtered rows in it
Hi to everyone,
The copyfilter code (copies the filtered rows from the active sheet to another sheet) works perfectly for my data. But I would like to modify one thing in it but could not do it with my limited vba knowledge and would like your help. This code doesn't create sheet(s) automatically for filtered criteria. But I want the code to create the sheet(s) as per filter criteria(s) and copy the filtered rows in it. Here is the code from www.contextures.com/xlautofilter03.html Sub CopyFilter() 'by Tom Ogilvy Dim rng As Range Dim rng2 As Range With ActiveSheet.AutoFilter.Range On Error Resume Next Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 End With If rng2 Is Nothing Then MsgBox "No data to copy" Else Worksheets("Sheet2").Cells.Clear Set rng = ActiveSheet.AutoFilter.Range rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _ Destination:=Worksheets("Sheet2").Range("A1") End If ActiveSheet.ShowAllData End Sub Please guide me. Regrads |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating sheet and copying filtered rows in it
Sub CopyFilter()
'by Tom Ogilvy Dim rng As Range Dim rng2 As Range Set OldSht = ActiveSheet With OldSht.AutoFilter.Range On Error Resume Next Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) FilterValue = rng2(0) On Error GoTo 0 End With If rng2 Is Nothing Then MsgBox "No data to copy" Else Sheets.Add after:=Sheets(Sheets.Count) Set NewSht = ActiveSheet NewSht.Name = FilterValue Set rng = OldSht.AutoFilter.Range rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _ Destination:=NewSht.Range("A1") End If OldSht.ShowAllData End Sub "shabutt" wrote: Hi to everyone, The copyfilter code (copies the filtered rows from the active sheet to another sheet) works perfectly for my data. But I would like to modify one thing in it but could not do it with my limited vba knowledge and would like your help. This code doesn't create sheet(s) automatically for filtered criteria. But I want the code to create the sheet(s) as per filter criteria(s) and copy the filtered rows in it. Here is the code from www.contextures.com/xlautofilter03.html Sub CopyFilter() 'by Tom Ogilvy Dim rng As Range Dim rng2 As Range With ActiveSheet.AutoFilter.Range On Error Resume Next Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 End With If rng2 Is Nothing Then MsgBox "No data to copy" Else Worksheets("Sheet2").Cells.Clear Set rng = ActiveSheet.AutoFilter.Range rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _ Destination:=Worksheets("Sheet2").Range("A1") End If ActiveSheet.ShowAllData End Sub Please guide me. Regrads |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating sheet and copying filtered rows in it
hi shabutt
See http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "shabutt" wrote in message ... Hi to everyone, The copyfilter code (copies the filtered rows from the active sheet to another sheet) works perfectly for my data. But I would like to modify one thing in it but could not do it with my limited vba knowledge and would like your help. This code doesn't create sheet(s) automatically for filtered criteria. But I want the code to create the sheet(s) as per filter criteria(s) and copy the filtered rows in it. Here is the code from www.contextures.com/xlautofilter03.html Sub CopyFilter() 'by Tom Ogilvy Dim rng As Range Dim rng2 As Range With ActiveSheet.AutoFilter.Range On Error Resume Next Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 End With If rng2 Is Nothing Then MsgBox "No data to copy" Else Worksheets("Sheet2").Cells.Clear Set rng = ActiveSheet.AutoFilter.Range rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _ Destination:=Worksheets("Sheet2").Range("A1") End If ActiveSheet.ShowAllData End Sub Please guide me. Regrads |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating sheet and copying filtered rows in it
Hi Joel,
Thank you for your time and help. Your code misses a few points I mentioned in my earlier post. 1- I want separate sheet(s) for each filter value, i.e., one sheet for one filter value, two sheets for two filter values and so on. 2- The sheet(s) should be named on filter value(s). Another request for you: How could the first row (column labels) be copied with the filtered data. Regards. "Joel" wrote: Sub CopyFilter() 'by Tom Ogilvy Dim rng As Range Dim rng2 As Range Set OldSht = ActiveSheet With OldSht.AutoFilter.Range On Error Resume Next Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) FilterValue = rng2(0) On Error GoTo 0 End With If rng2 Is Nothing Then MsgBox "No data to copy" Else Sheets.Add after:=Sheets(Sheets.Count) Set NewSht = ActiveSheet NewSht.Name = FilterValue Set rng = OldSht.AutoFilter.Range rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _ Destination:=NewSht.Range("A1") End If OldSht.ShowAllData End Sub "shabutt" wrote: Hi to everyone, The copyfilter code (copies the filtered rows from the active sheet to another sheet) works perfectly for my data. But I would like to modify one thing in it but could not do it with my limited vba knowledge and would like your help. This code doesn't create sheet(s) automatically for filtered criteria. But I want the code to create the sheet(s) as per filter criteria(s) and copy the filtered rows in it. Here is the code from www.contextures.com/xlautofilter03.html Sub CopyFilter() 'by Tom Ogilvy Dim rng As Range Dim rng2 As Range With ActiveSheet.AutoFilter.Range On Error Resume Next Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 End With If rng2 Is Nothing Then MsgBox "No data to copy" Else Worksheets("Sheet2").Cells.Clear Set rng = ActiveSheet.AutoFilter.Range rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _ Destination:=Worksheets("Sheet2").Range("A1") End If ActiveSheet.ShowAllData End Sub Please guide me. Regrads |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying one column with hidden/filtered rows to another colum | Excel Discussion (Misc queries) | |||
Copying filtered rows | Excel Programming | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
Trasnsposing or copying filtered data from one sheet to another | Excel Worksheet Functions | |||
Copying a Filtered Range from a Sheet in another Workbook | Excel Programming |