Macro to enter week day dates only

[Jimbo Slim]

I use XL2003

Each month I. have to enter dates for expenses etc into xl sheets.

I only require week dates ie never saturdays or sundays

I copy the previous months sheets rename to current month and use the
follwing macro I have to delete the old dates enter the dates for the new
month in th A column. Utilising Xls built in calender input box thingy.

At present .I then have to look up the saturdays and sundays in my dairy &
delete them then move up those days to remove the gaps manually pasting. I
have do do this to leave the table box s at the A column in place at the
table bottom.

I have tried to write and/or record a macro using the =weekday() function to
do this but it fails to work.

Any Ideas:

This macro enters all the days.

Sub EnterExpDates()
'
' EnterExpDates Macro
' Macro recorded 04/11/2007 by Jim Horslett
'

'
Range("A7").Select
Selection.AutoFill Destination:=Range("A7:A38"), Type:=xlFillDefault
Range("A7:A38").Select
Selection.ClearContents
Range("A6").Select
ActiveCell.Offset(1, 0).Select
Application.Run "PERSONAL.XLS!OpenCalendar"
Range("A7").Select
Selection.AutoFill Destination:=Range("A7:A38"), Type:=xlFillDefault
Range("A7:A38").Select

End Sub

 

[Guest]

Please try this. I do not have your calendar code but this does what you
require with a list of dates.

Sub EnterExpDates()
'
Cells(7, 1).Resize(31, 1).ClearContents
Range("A7").Value = Now
Range("A7").NumberFormat = "ddd dd/mm/yyyy"
Selection.AutoFill Destination:=Range("A7:A38"), Type:=xlFillDefault
For i1 = 38 To 7 Step -1
If Weekday(Cells(i1, 1).Value, vbMonday) > 5 Then _
Rows(i1).EntireRow.Delete
Next i1
End Sub

 

[Guest]

I see that you have resubmitted the same problem. Here is a slightly
different version which does the current month withou the use of a diary:
Sub EnterExpDates()
Cells(7, 1).Resize(31, 1).ClearContents
Range("A7").Value = Month(Now) & "/01/" & Year(Now)
Range("A7").NumberFormat = "ddd dd/mm/yyyy"
Range("A7").AutoFill Destination:=Range("A7:A38"), Type:=xlFillDefault
For i1 = 38 To 7 Step -1
If Month(Cells(i1, 1).Value) > Month(Now) Then
Rows(i1).EntireRow.Delete
ElseIf Weekday(Cells(i1, 1).Value, vbMonday) > 5 Then
Rows(i1).EntireRow.Delete
End If
Next i1
End Sub

 

[Jimbo Slim]

Thanks for all the answers I am still trying them out and attemping to adapt
& adopt these techniques into my sheet.
when I have more time I will explain more & clearer I hope thanx for all the
interest.

Jimbo

 

[Jimbo Slim]

Hi Guys

Here is my final version which uses bits from all of you

this enters only week days NO SATS OR SUNS in UK format.ie dd/mm/yyyy

It would be much easier in USA in US format for dates. But that was not what
i wanted

Sub EnterZExpDates()
'
Dim Cell As Object

Cells(7, 1).Resize(31, 1).ClearContents
Range("A7:A38").NumberFormat = "dd/mm/yyyy"
Range("A7").Select
ActiveCell.Value = Application.InputBox("Enter Date")
Selection.TextToColumns Destination:=Range("A7"), DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 4), TrailingMinusNumbers:=True
For Each Cell In Selection
If IsDate(Cell.Value) Then
Cell.Value = DateValue(Cell.Value)
End If
Selection.AutoFill Destination:=Range("A7:A38"),
Type:=xlFillDefault

Next Cell
For i1 = 38 To 7 Step -1
If Weekday(Cells(i1, 1).Value, vbMonday) > 5 Then _
Rows(i1).EntireRow.Delete
Next i1


End Sub