Excel Macro VBA to copy filtered range?

Question

Excel Macro VBA to copy filtered range?

Answer 1

Depending on your version of Office, as the previous poster noted, having macros run when a workbook opens can be problematic. By default Excel does not allow macros to run without the user specifically allowing them. You can enable that behavior by modifying macro security and allowing all macros to run, but that it really not recommended. You could try placing a message on the first worksheet and then modifying that message once macro are enabled by the user, but that would require setting the message again on the workbook close event.

Answer 2

Normally what I do many times is run the macro recorder and sometimes just simplify or slightly modify it's code. Here's some code the Macro Recorder made to filter some data and copy it to two columns on another worksheet:



Cells.Select

Selection.AutoFilter

Selection.AutoFilter Field:=1, Criteria1:="John"

Columns("A:B").Select

Selection.SpecialCells(xlCellTypeVisible).Select

Selection.Copy

Sheets("Sheet2").Select

Range("A1").Select

ActiveSheet.Paste

Range("C7").Select



That could be reduced down to:



Cells.AutoFilter Field:=1, Criteria1:="John"

Columns("A:B").SpecialCells(xlCellTypeVisible).Copy

Sheets("Sheet2").Range("A1").Paste



Basically, if you look at what I did. I got rid of all the Selects and I'm telling Excel how to do it directly.



Cells.Select

Selection.AutoFilter

Selection.AutoFilter Field:=1, Criteria1:="John"



If you take out all of the selecting and extra steps on this, it becomes:



Cells.AutoFilter Field:=1, Criteria1:="John"



On the next section:



Columns("A:B").Select

Selection.SpecialCells(xlCellTypeVisible).Select

Selection.Copy



Removing all of the selecting gives you:



Columns("A:B").SpecialCells(xlCellTypeVisible).Copy



By elimininating all of the selecting you make the macro shorter and make it do less process and thereby take up less space and run faster.





Now, if I wanted to copy over the data I put into Columns A and B in Sheet 2, it would be best to first clear the contents of Columns A and B on Sheet2. So I might change it to something like this:



Sheets("Sheet2").Columns("A:B").ClearContents

Sheets("Sheet1").Cells.AutoFilter Field:=1, Criteria1:="John"

Columns("A:B").SpecialCells(xlCellTypeVisible).Copy

Sheets("Sheet2").Range("A1").Paste

Answer 3

Do you wish to replace the data each week or append the new data to the existing data?

Answer 4

If you have a blank row and column below and to the right of the data you are filtering,



"



Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlToRight)).Select

End Sub"



works for the selection part.



Try AutoFiltering your data to quickly update the selection criteria and the resulting filtered data.



To do that, click anywhere in the 'database,' then select Data- Filter - AutoFilter.