Iteration: matching headers with worksheet names

[Tyrolpath]

I have a fairly complex macro (for me) to program.

The Workbook contains the following:

• The first Worksheet called “Export” contains 53 columns with headers in row 1 with names such as “Apples”, “Bananas”, “Cherry”, etc.
• The Workbook also contains 53 hidden Worksheets named like the headers in the “Export” Worksheet, i.e. “Apples”, “Bananas”, “Cherry”, etc.

The idea of the macro would be to:

• loop through the columns in Worksheet “Export”
• check if there is data in the column, e.g. in column “Apple” below the header in row 2
• copy all the data in this column
• loop though the hidden worksheets
• find and unhide the “Apple” worksheet
• paste the data in cell A1 in Worksheet “Apple”
• continue the loop and do the same for all the 53 columns in Worksheet “Export”

To go around the fact that I don’t know how to program this macro, I use a formula to link the content of the cells from Worksheet “Export” in the cells in Worksheets “Apple”, “Bananas”, etc. And I run a simple macro to unhide the worksheets if data is present in row 2 in the Worksheet “Export”. This is sub-optimal, especially if columns are added or removed in the Worksheet “Export”.

--> I would really appreciate if someone could give me hint on how to loop through the headers and more importantly on how to match the names of the headers and the name of the worksheets.

Thank you in advance for the help!

 

[DanteAmor]

Sheets can remain hidden, you don't need to show them.
Try this:

Sub matching_headers_with_sheets()
  Dim shE As Worksheet
  Dim c As Range
  Dim lr As Long
  
  Set shE = Sheets("Export")
  For Each c In shE.Range("A1", shE.Cells(1, Columns.Count).End(1))
    If Evaluate("ISREF('" & c.Value & "'!A1)") Then
      lr = shE.Cells(Rows.Count, c.Column).End(3).Row
      If lr > 1 Then
        Sheets(c.Value).Range("A1").Resize(lr - 1).Value = shE.Cells(2, c.Column).Resize(lr - 1).Value
      End If
    End If
  Next
End Sub

 

[Tyrolpath]

Dear Dante,

thanks a lot for taking the time to look into this.

The ISREF did the trick! thank you!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.