For Each WS ....

L

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
 
S

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.
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top