searching for data in columns with the same argument

[sjoerd.bosch]

Hello.
I am trying to copy/paste data from 2 columns (containing several rows with required data) and transfer the columns to another worksheet in the workbook
over a time span of either 23-24-or 25 hrs
The problem is that I need to use an argument what is not consistent and I have a macro in the workbook what adds a column at the end each time I enter new data.

In the attached I have the source sheet, which is here called "report"and the destination sheet, what is called "for technical report"

In row 6 there are mentioned occasions - which are named in the cell as: noon, eosp, sosp, departure, arrival and some others, which are not important
As said, when I update the daily reports - there is a column added at the end. Thus every day and every occasion a column is added in the sheet "reports'.

What I am looking to do is the following:
Copy / Paste the LAST Noon report and the previous (before the LAST) Noon report to a new column in another sheet, so that I have in the new columns the data in the columns of the last - either 23/24/25 hours (depends if we shift time when travelling East or West). If I have that data in another sheet, i can extract the data what I need on a daily basis.
It doesn't have to contain formulae. Just the data is sufficient
I have tried all sorts, both with macros and the build in formulas, but I am getting nowhere.
Hope you can assist

 

[JLGWhiz]

Which code are you using, Post #18 or Post #19? and by first colu7mn I assume you mean Columbn B with the event and item descriptions.

This is Post #18 code mocified to incluse column B. as 1st colmun on sheet2.

Sub t8()
Dim sh1 As Worksheet, sh2 As Worksheet, fn As Range, x As Long, col As Long
Set sh1 = Sheets("Report")
Set sh2 = Sheets("for technical report")
col = sh1.Cells(6, 1).End(xlToRight).Column + 1
Set fn = sh1.Rows(6).Find("Noon", sh1.Cells(6, col), xlValues, xlWhole, , xlPrevious)
    If Not fn Is Nothing Then
        sh2.Columns(1) = sh1.Columns(2).Value
        x = 3
        Do
            sh2.Columns(x) = sh1.Columns(fn.Column).Value
            Set fn = sh1.Rows(6).FindPrevious(fn)
            x = x - 1
        Loop While x <> 1
    End If
End Sub

 

[JLGWhiz]

In case you were using Post #19 code.

Sub t9()
Dim sh1 As Worksheet, sh2 As Worksheet, fn As Range, x As Long, col As Long
Set sh1 = Sheets("Report")
Set sh2 = Sheets("for technical report")
Set fn = sh1.Rows(4).Find(Format(Date, "d.m.yyyy"), sh1.Cells(4, Columns.Count), xlValues, xlPart, , xlPrevious)
    If Not fn Is Nothing Then
        col = fn.Offset(, 1).Column
    Else
        MsgBox "Date Not Found, Correction Needed." & vbLf & "Procedure will Terminate!", vbCritical, "NO DATE"
        Exit Sub
    End If
Set fn = Nothing
Set fn = sh1.Rows(6).Find("Noon", sh1.Cells(6, col), xlValues, xlWhole, , xlPrevious)
    If Not fn Is Nothing Then
        sh2.Columns(1) = sh1.Columns(2).Value
        x = 3
        Do
            sh2.Columns(x) = sh1.Columns(fn.Column).Value
            Set fn = sh1.Rows(6).FindPrevious(fn)
            x = x - 1
        Loop While x <> 1
    End If
End Sub

 

[sjoerd.bosch]

Hi. I am using the one from post #18 - and it works perfectly. Will also check #19 later.
Many thanks for your excellent help. Much appreciated. Makes me also realise I still need to learn a lot

 

[JLGWhiz]

Hi. I am using the one from post #18 - and it works perfectly. Will also check #19 later.
Many thanks for your excellent help. Much appreciated. Makes me also realise I still need to learn a lot

You're welcome,
Regards, JLG