For Each WS ....

[Ludo]

Hi,

I would like to add a column in all my worksheets in the active
workbook.
Then i'll apply the Autofilter on the first sheet.

This is the code i'll use, but it works only for the selected sheet
and not for the other ones.
What's missing or wrong here?

Sub FilterData()

'
Dim Ws As Worksheet

Sheets("sheet1").Select
ActiveSheet.Name = "DataImport1"
For Each Ws In ThisWorkbook.Worksheets

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "'-"
Range("A1").Select
Selection.Copy
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
Next Ws

Application.CutCopyMode = False
Columns("A:E").Select
Selection.AutoFilter
Range("A1").Select
Sheets.Add
ActiveSheet.Name = "Clean Data"
Sheets("DataImport1").Select
Application.ScreenUpdating = True

End Sub

Any help welcome.
Regards,
Ludo

 

[Sam Wilson]

You have this:

For Each Ws In ThisWorkbook.Worksheets
Columns("A:A").Select
...
Next

You want this:

For Each Ws In ThisWorkbook.Worksheets
Columns("A:A").Select
ws.columns("A:A")
...
next

etc.

 

[Patrick Molloy]

Sub FilterData()

Dim Ws As Worksheet
Sheets("sheet1").Name = "DataImport1"
For Each Ws In ThisWorkbook.Worksheets
WITH ws
.Columns("A:A").Insert Shift:=xlToRight
. Range(.Range("A1"), .Range("A1").End(xlDown)) = "'-"
END WITH
Next Ws

Columns("A:E").AutoFilter
Range("A1").Select
set ws =Sheets.Add
ws.Name = "Clean Data"

End Sub