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.
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