VBA Help

[mercmannick]

For Each ws In wkbkSS.Worksheets(Array("Everything", "Internal", "External", "S70"))
With ws.Rows("3:" & Rows.Count)
.AutoFilter
.ClearContents
.Interior.ColorIndex = xlNone ' clear contents
End With
With Range("A1")
.FormulaR1C1 = ws.Name & " WK " & CStr(VBAWeekNum(Now(), 1)) & "." & CStr(Application.WorksheetFunction.Weekday(Now())) - 2
End With
Next ws


Can anyone hlep me on this , it clears contents on all sheets but is not renaming correctly , it should be sheetname Wk (wknumber. Daynumber)

Thanks

Merc

[mercmannick]

ok sussed it
With wkbkSS.Worksheets("Everything").Range("A1")
.FormulaR1C1 = " IMF stage starts WK " & CStr(VBAWeekNum(Now(), 1)) & "." & CStr(Application.WorksheetFunction.Weekday(Now())) - 1
End With

With wkbkSS.Worksheets("Internal").Range("A1")
.FormulaR1C1 = " Internal Stage Starts WK " & CStr(VBAWeekNum(Now(), 1)) & "." & CStr(Application.WorksheetFunction.Weekday(Now())) - 1
End With

With wkbkSS.Worksheets("External").Range("A1")
.FormulaR1C1 = " External Kitting WK " & CStr(VBAWeekNum(Now(), 1)) & "." & CStr(Application.WorksheetFunction.Weekday(Now())) - 1
End With


With wkbkSS.Worksheets("S70").Range("A1")
.FormulaR1C1 = " IMF stage starts WK " & CStr(VBAWeekNum(Now(), 1)) & "." & CStr(Application.WorksheetFunction.Weekday(Now())) - 1
End With


how can i code this below...........

1. Delete row between header and data (row 5)

2. Columns B replace ?.? with ?/?
3. Set auto filter on
4. On short material col C, auto filter custom begins with L , T or M delete all rows
5. Rep order column F auto filter blanks and planned orders delete all rows
6. On outstanding ops column L if 0 delete all
7. On mrp code column D: auto filter B01-B05 change to S70, begins with I0 replace with S03C, begins with I4 replace with S03E, begins with I5 replace with S03F, begins with I6 replace with S03W all remaining begins with I7*-I99 replace with S03G, begins with B15 replace with S17



many thanks




Merc

[SamT]

Have you tried using

With Range("A1")
.Value= ws.Name & _
" WK " & _
CStr(VBAWeekNum(Now(), 1)) & _
"." & _
CStr(Application.WorksheetFunction.Weekday(Now())) - 2
End With

[mercmannick]

SamT

thanks that works also

any help on next part would be appreciated

how can i code this below...........

1.Delete row between header and data (row 5)

2.Columns B replace “.” with “/”
3.Set auto filter on
4.On short material col C, auto filter custom begins with L , T or M delete all rows
5.Rep order column F auto filter blanks and planned orders delete all rows
6.On outstanding ops column L if 0 delete all
7.On mrp code column D: auto filter B01-B05 change to S70, begins with I0 replace with S03C, begins with I4 replace with S03E, begins with I5 replace with S03F, begins with I6 replace with S03W all remaining begins with I7*-I99 replace with S03G, begins with B15 replace with S17



many thanks




Merc