Solved: Lookup Intersection and copy column data into another sheet

[LucasLondon]

Hello,

Basically I need to copy over data per column from one sheet and place it into another sheet, but I only want to copy subset of data, not entire columns based on the last populated cell in the destination sheet.

My setup is the following:

In the first sheet (Sourcedata) I have columns of data. Column A contains a list of dates and first row contains the names of each column/variable that I want copy data over for.

The second sheet (Result) contains a subset of data from the sourcedata sheet. In it, I have a list of dates in column M and variable names in the range c33:c38. The variables are also stored in the result sheet in Column O onwards with variable names populated in row 5.


The two sheets contain the following cross matching information in order to deterimine the row and column to copy over from:

Worksheet: Sourcedata Worksheet: Result
Column A - dates = Column M - dates
Row 1 - Headings = c33:c38 OR row 5 from column O onwards

I want to update the variables in the result sheet (Column O onwards) with data from the source sheet starting from the last populated date/row in column M.

So for each variable in the range c33:c38 (or in the range O5:T5) I want to find the equivalent column in the sourcedata sheet and copy over data from the last populated date in column M (and thus equivalent row in Souredata sheet).

For example, for the first variable (Diff-Volatility) in cell C33, I want to identify the corresponding column in the source sheet (Cell X1) and copy data from this column from row 69 to 73 only and place it in results sheet starting cell O53. Then I want to do the same for the next variable listed in C34.

Obviously I need some way to indentify the intersection between the row and the column to determine the starting point from which to copy data over from for each varaible. I have created the following macro to find the equivalent row where the dates match (row to start copying from) but I don't know how to determine the corresponding column to copy from.

[VBA]
Sub Firstpart()
Application.ScreenUpdating = False
Dim Rng As Range
Dim FindString As String
Dim i As Integer
Dim rw As Integer
'Find Starting date
Sheets("Result").Select
Range("M6").End(xlDown).Select
x = ActiveCell.Value
FindString = x
Sheets("Sourcedata").Select
Cells.Find(What:=FindString, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Select
rw = ActiveCell.Row
End Sub
[/VBA]

Hoping someone can help. Sorry for any confussion, have tried explain myself as clear as I can, hopefully the sample spreadsheet will make it more clear what I'm trying to do. The areas I want populate with new data (from the source sheet), I have coloured in yellow on the Result sheet.

Many Thanks,

Lucas

[RolfJ]

I took a look at your workbook and am puzzled by one thing. Your data in the 'SourceData' worksheet are nicely organized by date, one row at a time. The same holds for your 'Result' worksheet. However in your example for row 69 you are referring to multiple rows you want to transfer. Please explain.

[LucasLondon]

Hi Ralf,

That's a good point to raise. I should have mentioned that in the Sourcedata worksheet, althougth the last populated cell in Column A/Date is row 69, in terms of each of each of the variables/other columns there is data beyond row 69 which I want to copy over.

I can see why the dataset up my not make much sense. Basically the additional values you see beyond row 69 are forecasted datapoints for each indicator. I want to use them to predicts values in the column V of the result sheet by applying the (regression coefficients) in d33 to D38 to each new observation for each variables.

I have set up the formula up in the sheet (V54 onwards) so you can't see what the end goal is.

Thanks,

Lucas

[RolfJ]

As I said before, please post a workbook which shows the manually copied values from your 'SourceData' worksheet on your ''Result' worksheet. Otherwise we have to resort to mind-reading and I am not good at that.

[LucasLondon]

Hello,

As requested, I've updated the result sheet with the values that I wanted to pull in from the source sheet.

Regards,

Lucas

[RolfJ]

This macro might do the job for you:

[vba]
Sub CopyMostRecentData()
Dim sourceSheet As Worksheet, resultSheet As Worksheet
Set sourceSheet = Worksheets("SourceData")
Set resultSheet = Worksheets("Result")
Dim lastSourceRow As Long
lastSourceRow = sourceSheet.Cells(Rows.Count, "A").End(xlUp).Row

Dim rColumnNames As Range
Set rColumnNames = resultSheet.Range("C33:C38")
Dim rCell As Range
Dim rSource As Range, rTarget As Range
For Each rCell In rColumnNames
'First locate the corresponding columns in SourceData and Result worksheets
Set rSource = sourceSheet.UsedRange.Find(rCell.Value)
Set rTarget = resultSheet.Columns("N:W").Find(rCell.Value)
If Not rSource Is Nothing And Not rTarget Is Nothing Then
'Now set the ranges to copy from and paste to
Set rSource = Range(rSource.Cells(lastSourceRow, 1), rSource.Cells(Rows.Count, 1).End(xlUp))
Set rTarget = resultSheet.Cells(Rows.Count, rTarget.Column).End(xlUp).Offset(1)
rSource.Copy Destination:=rTarget
End If
Next rCell
End Sub

[/vba]

Let me know if you need it to be modified in any way.

[LucasLondon]

Hello Rolf,

I've tested the code and works great.

Thanks,

Lucas