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]

copy this to code module 1 and see if it will do what you want.

Sub t()
Dim fn As Range
Set fn = Sheets("Report").Rows(6).Find("Noon", Cells(6, Columns.Count), xlValues, xlWhole, , xlPrevious)
    If Not fn Is Nothing Then
        Sheets("Report").Cells(1, fn.Column - 1).Resize(, 2).EntireColumn.Copy
        Sheets("for technical report").Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlPasteValues
    End If
End Sub

 

[sjoerd.bosch]

Hi
I am getting an error in the line
Set fn = Sheets("Report").Rows(6).Find("Noon", Cells(6, Columns.Count), xlValues, xlWhole, , xlPrevious)

 

[JLGWhiz]

What is the error message?

 

[JLGWhiz]

I just noticed that I omitted a line of code that would remove the "marching ants" from the copy range. I also modified the line you were getting an error on, although it ran without error in my test set up.

Sub t()
Dim fn As Range
Set fn = Sheets("Report").Rows(6).Find("Noon", Cells(6, Columns.Count - 1), xlValues, xlPart, , xlPrevious)
    If Not fn Is Nothing Then
        Sheets("Report").Cells(1, fn.Column - 1).Resize(, 2).EntireColumn.Copy
        Sheets("for technical report").Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End If
End Sub

 

[sjoerd.bosch]

I just noticed that I omitted a line of code that would remove the "marching ants" from the copy range. I also modified the line you were getting an error on, although it ran without error in my test set up.

Sub t()
Dim fn As Range
Set fn = Sheets("Report").Rows(6).Find("Noon", Cells(6, Columns.Count - 1), xlValues, xlPart, , xlPrevious)
    If Not fn Is Nothing Then
        Sheets("Report").Cells(1, fn.Column - 1).Resize(, 2).EntireColumn.Copy
        Sheets("for technical report").Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End If
End Sub

 

[sjoerd.bosch]

It returns with runtime error 9 saying:
Subscript out of range

 

[JLGWhiz]

That error means it cannot find an object that is called out in the statement. It can be a misspelled sheet name or a name in the code that does not exist in the workbook. Check your sheet name tab to make sure the sheet name does not have leading or trailing spaces. To be sure, just double click in the name tab, then retype the name ensuring that you do not have leading or trailing spaces. If that does not fix the problem, then you might have to step through the code line by line, using the F8 function key and open the immediate window to find if all the variables are holding the correct values as the code executes. As I previously stated, it ran without error and produced the expected results in my test set up which basically copied the image you postes for sheet "Report".

 

[sjoerd.bosch]

Hi. Yes. You are correct. The sheet name was incorrect. The code does find the last and previous noon column and does paste it into the other sheet, but it doesn’t do what I wanted. Sorry.
You see. My main issue is that if we make a report what is not called noon, but for example arrival, or sosp, or any name really, it has to find the column before that called noon and copy that.
that column must then go into the other sheet as the first column and the last noon as the second column
Hope that makes sense

 

[sjoerd.bosch]

In other words. If i have lets say 10 columns with 20 rows or so and column 5 is called noon and then columns 6,7,8,9 are named differently and 10 is then again called noon, i want column 5&10 to be copied and pasted into another sheet. Column 5 being the first column and 10 the second.
maybe there is a code what can ignore the columns not named “noon” and finds the next one what is?