Link to home
Start Free TrialLog in
Avatar of Seamus2626
Seamus2626Flag for Ireland

asked on

Code to delete rows, add date and loop

Hi, i have attached a ss where i need some code to do the following with the three tabs highlighted green

(1) delete the first 3/4 rows so my headings are on the row 1
(2) Add a column named "Date" in Col AO and put in the current month in format mmm-yy

Loop through the three tabs highlightyed green doing the same

Thanks
LoopingSheetsCode.xlsx
Avatar of Rgonzo1971
Rgonzo1971

Hi,

pls try

Sub Macro1()
'
' Macro1 Macro
'


For Each sh In ThisWorkbook.Sheets
    If sh.Tab.Color = 5296274 Then
        sh.Activate
        Set Found = Range("A:A").Find("MasterGroup Name")
        Range(Range("A1"), Found.Offset(-1)).EntireRow.Delete
        With Range("AO1")
            .Value = "Date"
            .Interior.Color = vbRed
            .Font.Color = vbWhite
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .EntireColumn.NumberFormat = "MMM-yy"
        End With
    End If
Next
    

End Sub

Open in new window

Regards
Avatar of Seamus2626

ASKER

Rgonzo, that looks great but i must note that i only coloured the sheets green, going forward they would have to referred to explicitly by their name!

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hey Rgonzo,

The code is jumping straigh to Case Else?

Is that running fine for you?

Thanks
Running fine by me

Let's try

on your example I've inserted the code

Regards
Thanks!