Excel to find a text value in a sheet, copy the corresponding column and paste the entire column in another sheet
I have set up an Excel file where I am pasting data from another workbook into sheet 1
and then sheet 2
set to display specific values with
A1 =IF(NOT(ISBLANK(Sheet1!B1)),Sheet1!B2,"")
Throughout the worksheet (while changing cell references to suit my needs).
Usually this will work just fine for me, the problem is that sometimes when I load new raw data and copy it to sheet 1
, the columns in have sheet 1
moved to me due to random new columns being added and my formula for sheet 2
breaks down as it is set just to references to the relevant column from sheet 1 that I selected, not the actual data I need.
Below is an example:
Sheet 1 (Day 1) (just copying the raw data into 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 yield using the previously mentioned 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 to 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) (final result 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 almost 20 thousand rows, it is impractical for me to either search for changed columns in sheet 1, or change my formulas every time I get new data in sheet 2 ...
So my question is, can anyone suggest a way to write a macro in Excel to look for a text value in row 1 of sheet 1, copy the entire column containing that value, and paste the entire column into sheet 2
? I am fairly confident in using formulas in Excel, but have almost no knowledge of macros and really appreciate some help.
source to share
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
source to share