View Full Version : VBA Help
mercmannick
04-28-2007, 01:13 PM
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
04-28-2007, 01:30 PM
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
Have you tried using
With Range("A1")
.Value= ws.Name & _
" WK " & _
CStr(VBAWeekNum(Now(), 1)) & _
"." & _
CStr(Application.WorksheetFunction.Weekday(Now())) - 2
End With
mercmannick
04-29-2007, 09:36 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.