VBA - Using a block of code on a filtered list

[gavhanna]

Hi,

Basically what I am trying to do is filter a block of data and run a piece of code on 7 of the filtered items.

Is there a way (using loops or an array??) to do this without having to do the block of code 7 times (the only thing that will change will be the filtered name.

Thanks
Gav

 

[Andrew Poulsom]

Welcome to MrExcel.

What code do you want to run for the filtered items?

 

[gavhanna]

Basically I want to copy the filtered data into a new sheet, bring up a pop up box to let you select where to save the file.

Here is the code im using, im self thought on VBA so the code is probably terrible.

Sheets("Report1 (2)").Select
ActiveSheet.Range("$A$4:$L$4743").AutoFilter Field:=2, Criteria1:= _
"ZONA C.I.S."
Rows("4:4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste
Range("C2:H2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Macro").Activate
Sheets("Template1").Select
Range("A11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

ActiveWindow.ActivateNext
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
ActiveWindow.ActivateNext