Excel macro to find text value in sheet, copy the respective column, and paste whole column on another sheet

Question

I have set up an Excel file where I paste data from another workbook into sheet 1, and sheet 2 is then set to show certain values by using

A1 =IF(NOT(ISBLANK(Sheet1!B1)),Sheet1!B2,"") 

Throughout the whole sheet (with the cell references changing according to my needs).

Normally this would work for me just fine, the problem is that sometimes when I download the new raw data and copy into sheet 1, the columns in sheet 1 have moved on me due to the addition of random new columns and my formula for the sheet 2 breaks as it is set simply to reference the respective column from sheet 1 that I have selected, rather than the actual data that I need.

Below is an example:

Sheet 1 (Day 1) (simply copying the raw data to my workbook)

  |   A   |   B   |   C   |   D   |   E   |   F   |
--+-------+-------+-------+-------+-------+-------+
1 | Cat 1 | Cat 2 | Cat 3 | Cat 4 | Cat 5 | Cat 6 |
2 |   1A  |   1B  |   1C  |   1D  |   1E  |   1F  |
3 |   2A  |   2B  |   2C  |   2D  |   2E  |   2F  |
4 |   3A  |   3B  |   3C  |   3D  |   3E  |   3F  |

Sheet 2 (Day 1) (desired output using previously noted formula)

  |   A   |   B   |   C   |
--+-------+-------+-------+
1 | Cat 2 | Cat 6 | Cat 4 |
2 |   1B  |   1F  |   1D  |
3 |   2B  |   2F  |   2D  |
4 |   3B  |   3F  |   3D  |

Sheet 1 (Day 2) (copying the raw data the next day or so)

  |   A   |   B   |   C   |   D   |   E   |   F   |   G   |
--+-------+-------+-------+-------+-------+-------+-------+
1 | Cat 1 |Random | Cat 2 | Cat 2 | Cat 4 | Cat 5 | Cat 6 |
2 |   1A  |   1X  |   1B  |   1C  |   1D  |   1E  |   1F  |
3 |   2A  |   2X  |   2B  |   2C  |   2D  |   2E  |   1F  |
4 |   3A  |   3X  |   3B  |   3C  |   3D  |   3E  |   1F  |

Sheet 2 (Day 2) (resulting output due to new unwanted column)

  |   A   |   B   |   C   |
--+-------+-------+-------+
1 |Random | Cat 5 | Cat 3 |
2 |   1X  |   1E  |   1C  |
3 |   2X  |   2E  |   2C  |
4 |   3X  |   3E  |   3C  |

Due to the fact that I am dealing with over 100 columns of data and sometimes nearly 20 thousand rows, it is impractical for me to either search for the changed columns in sheet 1 or modify my formulas every time I get new data in sheet 2.

So my question is, could anyone suggest a way to write a Macro in Excel so as to search for a text value in row 1 of sheet 1, copy the entire column containing the value, and pasting the entire column in sheet 2? I am fairly confident in the use of formulas in Excel but have almost no knowledge on macros and would really appreciate some help.

Answer 1

Can you try using this macro:

Sub Macro1()
'
' Macro1 Macro
'

    Dim cell As Range
    For i = 1 To 50
        Sheets("Sheet1").Select
        If Cells(1, i).Value = "Cat 2" Then
        Columns(i).Select
        Selection.Copy
        Sheets("Sheet2").Select
        Range("A1").Select
        ActiveSheet.Paste
        End If
        If Cells(1, i).Value = "Cat 6" Then
        Columns(i).Select
        Selection.Copy
        Sheets("Sheet2").Select
        Range("B1").Select
        ActiveSheet.Paste
        End If
        If Cells(1, i).Value = "Cat 4" Then
        Columns(i).Select
        Selection.Copy
        Sheets("Sheet2").Select
        Range("C1").Select
        ActiveSheet.Paste
        End If
    Next i
End Sub