Copy Records from VBA Course

Hi,

Having recently gone through the basic VBA Course we were shown a method of copying records as below. I've managed to adadpt this for my needs however the data I wish to copy is held in a separate file / window, I've tried adjusting the code so it refences the other file/window/sheet, however it doesn't appear to work. For the time being I've adjusted the code, so that I move the other file into the VBA File and then carry out the copy records code before deleting the source. Is there a way of adjusting the below, so I can switch workbooks/files without having to import the data as a whole?

Many thanks

Joe Martin

Sub CopyRecords()

Dim strMan As String
'Can be seen by any procedure by any module in the workbook
Dim intRowCount As Integer
Dim intColumnCount As Integer
Dim intTargetRowCount As Integer


Dim strSourceRangeStart As String
Dim strSourceRangeFinish As String
Dim strTargetRangeStart As String
Dim strTargetRangeFinish As String

Dim strSourceRange As String
Dim strTargetRange As String

intTargetRowCount = 2
strMan = "MAT"


Windows("TOTALS_MAT.XLS").Activate

'to count number of row

For intRowCount = 1 To Sheets("TOTALS_MAT").Range(strMan) _
.CurrentRegion.Rows.Count

'How many rows in the region (CNTL+*) where cell A10 sits in.

If Sheets("Total Sales").Range("A10") _
.Cells(intRowCount, 10).Value = strName Then

'Count if StrName = the one we're looking for? in 10th column

strSourceRangeStart = _
Sheets("Total Sales").Range("A10"). _
Cells(intRowCount, 1).Address

'pick up address property, then same row to column you need
'Get addresses of start of source range

strSourceRangeFinish = _
Sheets("Total Sales").Range("A10"). _
Cells(intRowCount, 9).Address
'Get addresses of end of source range

strTargetRangeStart = _
Sheets(strName).Cells(intTargetRowCount, 1).Address
'Get Target Range Start of source range

strTargetRangeFinish = _
Sheets(strName).Cells(intTargetRowCount, 9).Address
'Get Target Range Finish of source range

strSourceRange = strSourceRangeStart _
& ":" & strSourceRangeFinish

'Creates Ranges where range is that we want to copy

strTargetRange = strTargetRangeStart _
& ":" & strTargetRangeFinish

'Creates Ranges, where to copy

Sheets(strName).Range(strTargetRange).Value = _
Sheets("Total Sales").Range _
(strSourceRange).Value

'copies to strName sheet

Sheets(strName).Cells(intTargetRowCount, 10) = _
Markup(Sheets(strName).Cells(intTargetRowCount, 8), _
Sheets(strName).Cells(intTargetRowCount, 9))

'Adds in Markup MARKUP(DP,SP) - Functions

Sheets(strName).Cells(intTargetRowCount, 11) = _
Commission(Sheets(strName).Cells(intTargetRowCount, 10))


intTargetRowCount = intTargetRowCount + 1
'next blank row in str sheet, next time copy goes onto next row

End If
Next intRowCount

End Sub