Hiding and Unhiding Columns

[BanburyS]

Good evening

So I have been trying to do this all day. I have tried loops etc. but cannot get my head around how to do this. I am a novice to VBA.

I have 2 workbooks

What I need to do is compare the values of say row 1 and columns A to BN1 in say sheet 1 in Workbook 1, and if the value is the same in Workbook 2 sheet 1 to keep the column unhidden. But if the values are different then to hide the column, so at the end the unhidden columns will be he same in both Workbooks. Hope that makes sense.

Any help would be greatly appreciated.

Kindest regards

 

[JLGWhiz]

Comparing row 1 values in sheet 1 of two workbooks. Range A1:BN1 in both. Hide entire column in workbook 1 and 2 for mismatched cells.

Sub hideCol()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Workbooks(1).Sheets(1)
Set sh2 = Workbooks(2).Sheets(2)
sh1.Columns.Hidden = False
sh2.Columns.Hidden = False
    For Each c In sh1.Range("A1:BN1")
        If c.Value <> sh2.Range(c.Address).Value Then
            c.EntireColumn.Hidden = True
            sh2.Columns(c.Column).Hidden = True
        End If
    Next
End Sub

 

[BanburyS]

Good morning JLGWhiz and others

Thank you for the very quick response and VBA code as below. Unfortunately, I have been unable to make this work as required. I have no doubt this is due to my poor explanation of what I am trying to achieve.

I am attaching 2 example Workbooks MasterData.xlsx and WorkingData.xlsm

WorkingData.xlsm needs to have a button that when pressed it will hide all columns in MasterData.xlsx except for the ones where the data in row 4/5 within WorkingData.xlsm match the data in row 1 within MasterData.xlsx

It need to do this irrespective of the sheet labels as they may not always be the same in MasterData.xlsx

I hope this goes somewhat further to explain what I need achieve.

Once again help from anyone would be very much appreciated.

Oh I have just found out I can not send attachments.

Kind regards BanburyS

Comparing row 1 values in sheet 1 of two workbooks. Range A1:BN1 in both. Hide entire column in workbook 1 and 2 for mismatched cells.

Sub hideCol()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Workbooks(1).Sheets(1)
Set sh2 = Workbooks(2).Sheets(2)
sh1.Columns.Hidden = False
sh2.Columns.Hidden = False
    For Each c In sh1.Range("A1:BN1")
        If c.Value <> sh2.Range(c.Address).Value Then
            c.EntireColumn.Hidden = True
            sh2.Columns(c.Column).Hidden = True
        End If
    Next
End Sub

 

[JLGWhiz]

Oh I have just found out I can not send attachments.

The last word in my signature line below is "Attachments". Click on it.

 

[BanburyS]

The last word in my signature line below is "Attachments". Click on it.

I am lost, I can't see anything that helps in your attachments link.

I just want to click on a button and hide any columns that don't match from my range in my working sheet, but they need to be hidden in the master sheet. I don't want it to hide anything in my working sheet but only in the master sheet. The button will need to be in he working sheet.