Copy filtered rows in one worksheet and paste to other worksheet

**tsiddiq23** · 02-03-2013, 12:23 AM

Friends,
Totally new to the Excel VBA or Macros. Was just doing first Macro where I want to filter rows in one worksheet and copy them to other worksheet but macro copies all the row to other work sheet. Please see below and guide. Thanks for your help.

Sub Macro2()
'
' Macro2 Macro
'

'
    ActiveSheet.Range("$A$1:$Q$480").AutoFilter Field:=9, Criteria1:= _
        "<25/01/2013", Operator:=xlAnd
      Rows("2:480").Select
    Selection.Copy
    Sheets("Filter BL Date").Select
    Range("A2").Select
    ActiveSheet.Paste

**HaHoBe** · 02-03-2013, 03:27 AM

Hi, tsiddiq23,

welcome to ExcelForum. You maybe need to reduce the raneg to only the visible rows. While being in Excel, choose the range via Mouse, hit F5, press the left button (I only have a local german version, should read either Contents or options), choose visible cells from there, the press CTRL+Copy.

Using VBA you can use SpecialCells(xlCellTypeVisible) for reducing the range:

Sub Macro2a()
'
' Macro2 Macro
'

'
With ActiveSheet
    .Range("$A$1:$Q$480").AutoFilter Field:=9, Criteria1:="<25/01/2013", Operator:=xlAnd
    .Range("$A$2:$Q$480").SpecialCells(xlCellTypeVisible).Copy
End With
Sheets("Filter BL Date").Select
Range("A2").Select
ActiveSheet.Paste
End Sub

If there aren´t any formulas in the data you could use the destination directly after the Copy like

.Range("$A$2:$Q$480").SpecialCells(xlCellTypeVisible).Copy Sheets("Filter BL Date").Range("A2")

Could you please put code tags around the macro you´re showing? Thanks in advance.

Ciao,
Holger

**tsiddiq23** · 02-03-2013, 07:25 PM

Hi Ciao,
Thank you for your reply. I do not know how to use the modification you wrote above. See below is complete code that came out when I recorded the macro. I am filtering two columns (separately) in Active Sheet and past (one by one) the filtered rows in "Filter BL Date" worksheet next to Active sheet. I want the rows I get after filtering to be pasted to in "Filter BL Date" worksheet. Sorry, I am totally new to the macros so do not know much about VBA yet. Thanks again for your help.

Sub Macro2()
'
' Macro2 Macro
'

'
    ActiveSheet.Range("$A$1:$Q$480").AutoFilter Field:=9, Criteria1:= _
        "<25/01/2013", Operator:=xlAnd
    'Change above date to last date of the filter
    Rows("2:480").Select
    'Change range selection as per requirement
    Selection.Copy
    Sheets("Filter BL Date").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("C37").Select
    ActiveWindow.SmallScroll Down:=12
    Sheets("Main").Select
    ActiveWindow.SmallScroll Down:=-36
    Application.CutCopyMode = False
    ActiveSheet.ShowAllData
    'Above statement clears filter
    ActiveWindow.SmallScroll Down:=-84
    ActiveWindow.ScrollRow = 128
    ActiveWindow.ScrollRow = 126
    ActiveWindow.ScrollRow = 124
    ActiveWindow.ScrollRow = 122
    ActiveWindow.ScrollRow = 116
    ActiveWindow.ScrollRow = 112
    ActiveWindow.ScrollRow = 110
    ActiveWindow.ScrollRow = 105
    ActiveWindow.ScrollRow = 101
    ActiveWindow.ScrollRow = 99
    ActiveWindow.ScrollRow = 95
    ActiveWindow.ScrollRow = 92
    ActiveWindow.ScrollRow = 89
    ActiveWindow.ScrollRow = 87
    ActiveWindow.ScrollRow = 85
    ActiveWindow.ScrollRow = 83
    ActiveWindow.ScrollRow = 80
    ActiveWindow.ScrollRow = 79
    ActiveWindow.ScrollRow = 76
    ActiveWindow.ScrollRow = 74
    ActiveWindow.ScrollRow = 72
    ActiveWindow.ScrollRow = 70
    ActiveWindow.ScrollRow = 68
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 64
    ActiveWindow.ScrollRow = 63
    ActiveWindow.ScrollRow = 62
    ActiveWindow.ScrollRow = 61
    ActiveWindow.ScrollRow = 60
    ActiveWindow.ScrollRow = 58
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 56
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 53
    ActiveWindow.ScrollRow = 52
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    ActiveSheet.Range("$A$1:$Q$480").AutoFilter Field:=10, Criteria1:= _
        ">=29/12/2012", Operator:=xlAnd, Criteria2:="<=25/01/2013"
    Rows("2:441").Select
    Selection.Copy
    Sheets("Filter BL Date").Select
    ActiveWindow.SmallScroll Down:=90
    Range("A127").Select
    ActiveSheet.Paste
    Range("E126").Select
    ActiveWindow.ScrollRow = 102
    ActiveWindow.ScrollRow = 101
    ActiveWindow.ScrollRow = 100
    ActiveWindow.ScrollRow = 96
    ActiveWindow.ScrollRow = 91
    ActiveWindow.ScrollRow = 86
    ActiveWindow.ScrollRow = 82
    ActiveWindow.ScrollRow = 77
    ActiveWindow.ScrollRow = 71
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 63
    ActiveWindow.ScrollRow = 61
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 51
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Columns("A:Q").Select
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Application.CutCopyMode = False
    Range("A1:Q566").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    ActiveWindow.SmallScroll Down:=-6
End Sub

**Alf** · 02-03-2013, 03:33 AM

You could try to replace these lines in your macro:

Rows("2:480").Select
 Selection.Copy
 Sheets("Filter BL Date").Select
 Range("A2").Select
 ActiveSheet.Paste

with this:

ActiveSheet.AutoFilter.Offset(1).Copy
Sheets("Filter BL Date").Activate
Range(“A2”).PasteSpecial Paste:=xlPasteAll

If you wish to include the headings from the autofilter settings use

ActiveSheet.AutoFilter.Range.Copy

instead of

ActiveSheet.AutoFilter.Offset(1).Copy

Alf

Ps When you post code you should wrap it (forum rule)

**tsiddiq23** · 02-03-2013, 07:42 PM

Hi Alf,
Thanks for your reply. When I replaced the code with yours, it gave me Run_time error"1004". Method 'Range of object'_Global' failed. I think I am not understanding/using it properly. Will try again.
Regards,

Tahir

**HSV** · 02-03-2013, 08:52 PM

Put the date in Cell R1.

Sub hsv()
With ActiveSheet
 .Range("A1:Q480").AutoFilter 9, "<" & CLng(Range("R1"))
 .AutoFilter.Range.Offset(1).SpecialCells(12).Copy Sheets("Filter BL Date").Cells(Rows.Count, 1).End(xlUp).Offset(1)
 .Range("A1:Q480").AutoFilter
 End With
End Sub

**tsiddiq23** · 02-03-2013, 10:23 PM

Hi Harry,
Thanks for your reply. My Active sheet where my data resides (I call it 'Main' sheet) has row 1 as Fields and other rows contain data. 9th and 10th columns are date columns where I filter dates as required. You suggested to put date in cell R1. but R1 is one of the Fields heading. So, how it will work? Thanks again.

Regards,
Tahir

**HSV** · 02-04-2013, 05:13 AM

There are a lot cells in Excel, choose one.

Copy filtered rows in one worksheet and paste to other worksheet

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copy filtered rows in one worksheet and paste to other worksheet

Re: Copy filtered rows in one worksheet and paste to other worksheet

Re: Copy filtered rows in one worksheet and paste to other worksheet

Re: Copy filtered rows in one worksheet and paste to other worksheet

Re: Copy filtered rows in one worksheet and paste to other worksheet

Re: Copy filtered rows in one worksheet and paste to other worksheet

Re: Copy filtered rows in one worksheet and paste to other worksheet

Re: Copy filtered rows in one worksheet and paste to other worksheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions