VBA-Copying a column from one sheet to another conditionally

[FrostyBostie]

So I want to cycle through all the sheets, Check cell E1, and if it has "flex" within the string, it copies column B and appends it to the next column on the first page "Mastersheet".. not working for some reason, Seems fairly straight forward but i'm probably missing something. pretty new to this. Oh not sure if it makes a difference but A1-D1 and E1-N1 are 2 large merged cells



Thanks for any help.


This is cross-posted Here

Function fn_LastColumn

 Dim lastCol As Long
 lastCol = Sht.Cells.SpecialCells(xlLastCell).Column
 lCol = Sht.Cells.SpecialCells(xlLastCell).Column
 Do While Application.CountA(Sht.Columns(lCol)) = 0 And lCol <> 1
 lCol = lCol - 1
 Loop
 fn_LastColumn = lCol

End Function

On Error GoTo IfError

Dim Sht As Worksheet, DstSht As Worksheet
Dim LstRow As Long, LstCol As Long, DstCol As Long
Dim var As String

With Application
 .ScreenUpdating = False
 .EnableEvents = False
End With
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Sheets("Mastersheet").Delete
Application.DisplayAlerts = True

Sheets.Add(Before:=Sheets(1)).Name = "Mastersheet"

For Each Sht In ActiveWorkbook.Worksheets
 DstCol = fn_LastColumn(Mastersheet)
 
 If DstCol = 1 Then DstCol = 0
 
 var = Cells(1, 5).Value
 If InStr(var, "flex") Then
 Columns(2).Copy Sheets("Mastersheet").Cells(1, DstCol + 1)
 End If

 Next
 
IfError:
 With Application
 .ScreenUpdating = True
 .EnableEvents = True
 End With

End Sub

 

[Fluff]

How about

Sub FrostyBostie()
   Dim Ws As Worksheet, Mws As Worksheet
   Dim NxtCol As Long
   
   Application.ScreenUpdating = False
   If Evaluate("Isref(Mastersheet!A1)") Then
      Sheets("Mastersheet").Cells.Clear
   Else
      Sheets.Add(Sheets(1)).Name = "Mastersheet"
   End If
   Set Mws = Sheets("Mastersheet")
   For Each Ws In Worksheets
      If InStr(1, Ws.Range("E1"), "flex", vbTextCompare) > 0 Then
         NxtCol = NxtCol + 1
         Ws.Range("B:B").Copy Mws.Cells(1, NxtCol)
      End If
   Next Ws
End Sub

 

[FrostyBostie]

How about...

This did it. Thanks so much. Nice and clean too.

 

[Fluff]

You're welcome & thanks for the feedback