Macro to enter week day dates only

  • Thread starter Thread starter Jimbo Slim
  • Start date Start date
J

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
 
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
 
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
 
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
 
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
 
Back
Top