fifa 2006

[lior03]

hello
the follwing workbok was downloaded from the offical titer of fifa 2006
world cup.i was tring to build a macro that take all players of a certain type - goalkeepers of instance (denoted as GK) and copy them to a diffrent sheet.i allmost succeded.please help

[lior03]

let me refine my question
if i want all goalkepeers.denoted as GK.how can i prevent excel from copying every goalkepeer to a diffrent worksheet and instead to only one worksheet for all.
[VBA]
Sub fifa2006()
Sheets("2006").Range("A1").Activate
Selection.CurrentRegion.Select
Dim cell As Range
Dim x As String
Dim newsheet As Worksheet
x = InputBox("enter name")
For Each cell In Selection
If cell.Value = x Then
cell.EntireRow.Copy
Set newsheet = Worksheets.Add
newsheet.Range("A1").PasteSpecial Paste:=xlValues
End If
Next
End Sub

[/VBA]
thanks

[lucas]

Hello Lior,
This is a very useful script for copying multiple criteria to newly created sheets in the same workbook. It will move them all for you at one time after its set up. Just run the macro and look at the sheets to see what it is doing. I set up GK for you as an example....if you have trouble setting it up for the other players post back. Someone might have a better idea too....

[lucas]

Here is your file fixed. I hope this is what you had in mind.

[lior03]

hello
after 24 hours this is what i came up with.
[VBA]
Sub filterdata()
Selection.CurrentRegion.Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="FW"
Selection.Copy
Sheets.Add
ActiveSheet.Paste
ActiveSheet.Paste
Application.CutCopyMode = False
Columns.AutoFit
End Sub

[/VBA]
can anyone help simplfy it?
how can i name the sheet i created by the criteria name i used?
thanks

[lucas]

Guess the worksheet I posted wasn't what you were looking for...try this:
[VBA]Worksheets.Add(After:=PlayersWS).Name = "GK"[/VBA]
change the name of the sheet to add the new GK Sheet after...PlayersWS in the example.

[lucas]

[VBA]Dim Players As Worksheet
ActiveWorkbook.Sheets.Add After:=Worksheets("Players")
ActiveSheet.Name = "GK"[/VBA]

[lior03]

hello
please help me fix the following..........
[VBA]
Range("A1:C1250").AutoFilter Field:=2, Criteria1:="=module16"
selection.SpecialCells(xlCellTypeVisible).Copy
Sheets.add
ActiveSheet.paste
Columns.autofit
Rows.autofit
Dim lngrow As Long
lngrow = ActiveSheet.Range("a1").End(xlDown).row
Cells(lngrow + 3, 3).FormulaR1C1 = "=countA(R2C:R[-1]C)"

[/VBA]
thanks

[lior03]

hello
i am trying to export filtered data from a sheet (the activesheet)
to another sheet called - "NEW".
[VBA]On Error GoTo err
Application.ScreenUpdating = False
Dim rng1 As Range
If SheetExists("new") = True Then
With Worksheets("new").Activate
Cells.clear
Set rng1 = ActiveSheet.Range("A1").CurrentRegion
Set rng1 = rng1.SpecialCells(xlVisible)
rng1.Copy Destination:=Worksheets("new").Range("A1")
End With
End If
Exit Sub
err:
If Not SheetExists("new") = True Then
Sheets.Add.name = "new"
End If
Application.ScreenUpdating = True[/VBA]
please tell me how to refer to the original sheet other then activesheet to have the data exported.
thanks

[Bob Phillips]

[vba]

Dim rng1 As Range
Dim shThis As Worksheet

On Error GoTo err
Set shThis = ActiveSheet
Application.ScreenUpdating = False
If Not SheetExists("new") Then
Sheets.Add.Name = "new"
End If
With Worksheets("new")
.Activate
.Cells.Clear
Set rng1 = shThis.Range("A1").CurrentRegion
Set rng1 = rng1.SpecialCells(xlVisible)
rng1.Copy Destination:=.Range("A1")
End With

err:
Application.ScreenUpdating = True
[/vba]