Loop to copy prices from one workbook to another

[KleckerJPK]

I have created two workbooks: 1) a matrix with prices and 2) details which need to have the prices added. The prices need to be added based on type of loan, group rate, and month. There is a column (BC:BC) added on the 2nd wksheet that has this information in a code that will match codes on the 1st wkbook (col K) next to the appropriate prices indicating which line the price is on (in col E). On the 2nd wkbook there is multiple lines of data and also a cell that shows the count of the data lines (BD2). Here is my macro code that will find and copy the data for one iteration / row:

Sub MBS_Trades()
'
'Define variables
Dim TFile As String
Dim CLVFile As String
Dim CLVPath As String
Dim SFileName As String
Dim CellRange As String

Sheets("Assumptions").Select
TFile = Range("B8")
CLVPath = Range("B10")
CLVFile = Range("B12")
SFileName = Range("B14")



Windows(CLVFile & ".xlsx").Activate
Sheets("MBS Closed Loans Only").Select
Range("BC2").Select
Selection.Copy
Windows(TFile).Activate
Sheets("Assumptions").Select
Range("C31").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Calculate
CellRange = Range("B32")

Sheets("Matrix").Select
Range(CellRange).Select
Selection.Copy
Windows(CLVFile & ".xlsx").Activate
Range("S2").Select ' this is column where the price is to be placed - row 2 is first row
ActiveSheet.Paste
Windows(TFile).Activate

End Sub

I would like to create a loop that will go through each line of data in wkbk 2, read the code in wkbk 2, find the code and corresponding price in wkbk 1, and paste the price in wkbk 2.

Here is the assumptions tab where the variable definitions come from in wkbk 1:

col A	B	C	D	E
row 2	Current	Year	Month	Day
3 Today	03/24/2017	2017	03	24
4	201703	= Year Month
5	20170324	= Year Month Day
6
7 Filename / Path	H:\MBS Pricing Matrix\[2017-02 MBS EOD Pricing Matrix.xlsm]Assumptions
8 This File	2017-02 MBS EOD Pricing Matrix.xlsm
9
10 File Path	I:\NatRes\Shared\Secondary Reporting\QRM files\QRM Procedures\QRM Reconciliations\
11
12 File Tamplate	MBS Only for Closed Loans Validation
13
14 Save Template Name	MBS Only for Closed Loans Validation 03-24-2017
15
16 Report Month	3
17
18 Month vlookup	1	January	Jan
19	2	February	Feb
20	3	March	Mar
21	4	April	Apr
22	5	May	May
23	6	June	Jun
24	7	July	Jul
25	8	August	Aug
26	9	September	Sep
27	10	October	Oct
28	11	November	Nov
29	12	December	Dec
30
31 Row Copy Data	14	GNMA I4Mar
32 Cell Range	E14

<tbody>
</tbody>

The "14" in row 31 is a vlookup to find which row the code in cell C31 (GNMA I4Mar) is in on the table where the price is located. The "E14" on row 32 is the actual cell address of the price which needs to be copied in wkbk 1. The price needs to be pasted in the same row as the original code was found in wkbk 2.

I have used Excel extensively with complex formulas and also written many macros (VBA) but I am new to creating loops. Any help here would be greatly appreciated. Thanks in advance!

 

[Carpaccio]

Hi KleckerJPK
There is a lot of info in your post and I confess that I haven;t really followed it. I get that you want to loop through workbook 2, from the code there, find the matching code in workbook1 and grab the price, then return it to workbook 2.
only you don't have workbooks 1 or two, you have Assumptions and another that you find from a cell in workbook2?

Anyway, you were interested in looping, so this might give you a start:
Sub MBS_Trades()
'Define variables
Dim TFile As String
Dim CLVFile As String
Dim CLVPath As String
Dim SFileName As String
Dim CellRange As String 'this rang has sthis kind of data


Sheets("Assumptions").Select
TFile = Range("B8")
CLVPath = Range("B10")
CLVFile = Range("B12")
SFileName = Range("B14")


'go through each line of data in wkbk 2,
Dim MyRow As Integer
Dim myCol As Integer
Dim targetPrice As Long
Dim CodeToFind As Variant


MyRow = 2
myCol = 55 'column BC


While (Cells(MyRow, myCol)) <> "" 'loop down the rows untill the cell is empty
'find the code in wkbk 2
Windows(CLVFile & ".xlsx").Activate 'this is workbook 2
Sheets("MBS Closed Loans Only").Select
CodeToFind = Range("BC2").Value 'the first price to read

'in wkbk 1, find matching code from wkbk 2 and get the price
Windows(TFile).Activate
Sheets("Assumptions").Select
For i = 31 To 500 'the range to loop throgh
If Cells(i, 3).Value = CodeToFind Then
targetPrice = Cells(i, 3).Offset(0, 1).Value 'get the value from the cell same row, 1 column to the right
GoTo DoneFinding 'we don't need to look further
End If
Next i 'next row
DoneFinding:

'back to wkbk2 ad write the price
Windows(CLVFile & ".xlsx").Activate
Cells(MyRow, myCol).offfset(0, -36).Value = targetPrice ' this is column where the price is to be placed - row 2 is first row



Wend 'end While


End Sub

I hope it helps. Perhaps if you simplified the task to 2 worksheets with a couple of columns and got that working, then you could rearrange it to your real workbooks?

Regards
Carpaccio

 

[KleckerJPK]

Carpaccio;

This is great - I am able to use this to get the effect I want with slight adjustments. Thanks so much for your help!