1 Attachment(s)
Solved: Lookup Intersection and copy column data into another sheet
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
This might be what you are looking for
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.