remove columns whose headers don't match sheet name

[Kaps_mr2]

Hello,

I am trying to loop through all the sheets in a workbook and delete any columns where the value in row 1 is not equal to the sheet name. The data always starts in column 16 and extends for a variable number of columns to the right. My code is below:-

Public Sub remove_extra_categories()
Dim sh As Long
Dim c As Long
Dim category_data_range As Range
Dim sheet_name As String
Dim last_column_of_data As String
Dim current_column_header As String

For sh = 1 To no_of_categories
    sheet_name = output_workbook.Sheets(sh).Name
    last_column_of_data = output_workbook.Sheets(sh).Cells(1, Columns.Count).End(xlToLeft).Column - 13
    'MsgBox sheet_name & " " & last_column_of_data
    
    For c = last_column_of_data To 16 Step -1
        current_column_header = output_workbook.Sheets(sh).Range("o1").Offset(0, c).Value
        
        If current_column_header <> sheet_name Then
        output_workbook.Sheets(sh).Columns(c).Delete
        End If
        
    Next c
    
Next sh

 

[Kaps_mr2]

Where is the code that runs the User file select dialog?

Public Sub select_file()
Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    With fd
        .Filters.Clear
        .Title = "Select an Excel File"
        .Filters.Add "Excel Files", "*.xlsx", 1
        .AllowMultiSelect = False
        
        Dim sFile As String
    
        If .Show = True Then
            sFile = .SelectedItems(1)
        End If
    End With
    
    If sFile <> "" Then
        Workbooks.Open sFile     ' Open the Excel file.
        Set source_file = ActiveWorkbook
    End If

End Sub

 

[Skyybot]

Change wb to:

Set wb = ActiveWorkbook

It should work if the Workbook you want to edit is currently ActiveWorkbook. Otherwise, we may have to add code for user to select Workbook, or create a Class Module to store the Workbook name.

 

[Kaps_mr2]

Change wb to:

Set wb = ActiveWorkbook

It should work if the Workbook you want to edit is currently ActiveWorkbook. Otherwise, we may have to add code for user to select Workbook, or create a Class Module to store the Workbook name.

it doesn't work. I can't see why using : set wb =output_workbook doesn't work.

 

[Skyybot]

Because in that Sub you haven't defined the Variable output_workbook. Once the select_File Sub completes, all of its Variables are killed.

 

[Skyybot]

Because in that Sub you haven't defined the Variable output_workbook. Once the select_File Sub completes, all of its Variables are killed.

Example:

Dim output_workbook as Workbook
Set output_workbook = Workbooks("NameOfWorkbook.xlsx")