Results 1 to 6 of 6

Thread: Macro to loop thru sheets adding new columns until it hits particular sheet then stops

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro to loop thru sheets adding new columns until it hits particular sheet then stops

    Trying to write a macro that will add 2 new columns (A & B) to each sheet in the workbook, adding header names & color coding, but need it to stop when it reaches the tab named "Master"
    Below is the code I have so far, but keep getting a compile error in the 'Start loop' section

    Sub Insert_Columns()

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets

    On Error Resume Next

    'adds new column A & B into each worksheet
    ws.[a:b].Insert

    'places header in new columns
    ws.Range("A1").Value = "Date Deleted: After Master Tab is Created"
    ws.Range("B1").Value = "Date Added: After Master Tab is Created"

    'Color codes the header "RED" in column A
    With ws.Range("A:A").Font
    .Color = -16776961
    .TintAndShade = 0
    .Bold = True
    End With

    'Color codes the header "Green" in column B
    With ws.Range("B:B").Font
    .Color = -11489280
    .TintAndShade = 0
    .Bold = True
    End With

    With ws.Rows("1:1").EntireRow.AutoFit
    End With

    'Start loop
    For Each sht In ws.Worksheets
    'If worksheet in loop is the last one, stop execution (it is Master worksheet)
    If sht.Index = ws.Worksheets Then
    Exit For
    End If

    'when loop reaches "Master tab then it stops _
    copying tabs to master tab
    If sht.Name = "Master" Then
    Exit For
    End If

    Next ws

    End Sub

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,087
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Macro to loop thru sheets adding new columns until it hits particular sheet then stops

    Code:
    Sub Insert_Columns()
        
        Dim ws As Worksheet
        
        For Each ws In ActiveWorkbook.Worksheets
            
            If ws.Name = "Master" Then Exit For
            
            'adds new column A & B into each worksheet
            ws.[a:b].Insert
            
            'places header in new columns
            ws.Range("A1").Value = "Date Deleted: After Master Tab is Created"
            ws.Range("B1").Value = "Date Added: After Master Tab is Created"
            
            'Color codes the header "RED" in column A
            With ws.Range("A:A").Font
                .Color = -16776961
                .TintAndShade = 0
                .Bold = True
            End With
            
            'Color codes the header "Green" in column B
            With ws.Range("B:B").Font
                .Color = -11489280
                .TintAndShade = 0
                .Bold = True
            End With
            
            ws.Rows("1:1").EntireRow.AutoFit
            
        Next ws
        
    End Sub
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to loop thru sheets adding new columns until it hits particular sheet then stops

    So you just put the [code] Name= "Master" Then Exit For [code] at the beginning and don't need the loop thru section.
    Sweet. Appreciate the help.

  4. #4
    New Member
    Join Date
    Aug 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to loop thru sheets adding new columns until it hits particular sheet then stops

    Quote Originally Posted by Salts View Post
    So you just put the [code] Name= "Master" Then Exit For [code] at the beginning and don't need the loop thru section.
    Sweet. Appreciate the help.

    Want to use this same premise to go back and hide certain column in all worksheets except for "Master" sheet - except when I run the following code - it only works in the "Master" tab,
    it does not hide these columns in any of the other worksheets.

    [code]For Each ws In ActiveWorkbook.Worksheets

    If ws.Name = "Master" Then Exit For

    Range("A:B,D:D,L:L,N:N,Q:S,V:V,Y:Y,AB:AB,AE:AE,AK:AL").Select
    Selection.EntireColumn.Hidden = True

    Range("C1").Select

    Next ws
    End Sub [end code]

  5. #5
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,087
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Macro to loop thru sheets adding new columns until it hits particular sheet then stops

    Code:
        For Each ws In ActiveWorkbook.Worksheets
        
            If ws.Name = "Master" Then Exit For
        
            ws.Range("A:B,D:D,L:L,N:N,Q:S,V:V,Y:Y,AB:AB,AE:AE,AK:AL").EntireColumn.Hidden = True
        
        Next ws
    Last edited by AlphaFrog; Sep 7th, 2019 at 09:37 PM.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to loop thru sheets adding new columns until it hits particular sheet then stops

    Makes sense, simple enough & works perfectly. AlphaFrog - you are amazing & very helpful. Thank you!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •