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