Macro for Inserting Lines and Adding Dates

[ststern45]

I have a worksheet that has 1o workbooks (TABS)

What I would like to do is insert a line in each of the Tabs and add the current date in cell A10.

The information on each line has a range from cell A10 through CO10.

I have certain tabs depending on the day that I do not want to insert a line or the current date. For example: I have 2 tabs that I do not want a Sunday date.

Can this be accomplished??

Thanks in advance!!

Steve

 

[Von Pookie]

Try:

Sub test()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    With ws
        Select Case Weekday(Date)
            Case Is = 1 'if weekday is Sunday
                'if worksheet name is not one of these 2, insert row/date on all other sheets
                If .Name <> "No Sunday" And ws.Name <> "No Sunday2" Then
                    .Rows(10).EntireRow.Insert
                    .Range("A10").Value = Date
                End If
            Case Else 'if weekday is anything but Sunday
                'insert row/date in all sheets
                .Rows(10).EntireRow.Insert
                .Range("A10").Value = Date
        End Select
    End With
Next ws

End Sub

 

[ststern45]

Thank you!! Additional Question

Can you also add a line that will drag all the formulas from line 11 (previously line 10) cell ranges b10 through co10.

Thanks again,

Steve

 

[Von Pookie]

Try:

Sub test()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    With ws
        Select Case Weekday(Date)
            Case Is = 1
                If .Name <> "No Sunday" And ws.Name <> "No Sunday2" Then
                    .Rows(10).EntireRow.Insert
                    .Range("A10").Value = Date
                    .Range("B11:CO11").AutoFill Destination:=.Range("B10:CO11")
                End If
            Case Else
                .Rows(10).EntireRow.Insert
                .Range("A10").Value = Date
                .Range("B11:CO11").AutoFill Destination:=.Range("B10:CO11")
        End Select
    End With
Next ws

End Sub

 

[ststern45]

Error Message "cannot change part of an array"

Thanks for the code.

I get the above error message: Cannot change part of an array

Sub test()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
With ws
Select Case Weekday(Date)
Case Is = 1
If .Name <> "No Sunday" And ws.Name <> "No Sunday2" Then
.Rows(10).EntireRow.Insert
.Range("A10").Value = Date
.Range("B11:CO11").AutoFill Destination:=.Range("B10:CO11")
End If
Case Else
.Rows(10).EntireRow.Insert 'This where the program stops'
.Range("A10").Value = Date
.Range("B11:CO11").AutoFill Destination:=.Range("B10:CO11")
End Select
End With
Next ws

End Sub

Thanks for all your help

Steve

 

[Von Pookie]

That code is working for me in both 2002 and 2003...

What line of the code is highlighted when the error occurs?
Are you using array formulas in the cells?