searching for data in columns with the same argument

sjoerd.bosch

New Member
Joined
Feb 9, 2012
Messages
49
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
 

Attachments

  • source sheet.png
    source sheet.png
    32.7 KB · Views: 21
  • destination sheet.png
    destination sheet.png
    36.8 KB · Views: 20
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.
VBA Code:
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
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
In case you were using Post #19 code.

VBA 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
 
Upvote 0
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 :)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top