LucasLondon
10-31-2009, 07:50 AM
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.
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
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
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.
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
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