VBA copy Macro from existing files and copy to stats file

[snyper189]

I am having issues with the following Macro. Little bit of information. I have a workbook called '2015 Stats' and all of my Officers compile a 'Patrol Log' and save it to a folder. they save it in the format of the date and their last name (i.e. 05-12-15 Smith). I succeeded in getting the macro to open the correct files copy and paste the data to the correct location however, it seems the macro is copying data from 4 cells down of my target location. For example, when it opens my first file that exists for specified date and officer, I tell it to copy cell E50 but it copies cell E54.


the reasoning behing the rng.copy sh.cells(I, 4) is because I want the data to be pasted in the row corresponding with the date that was just used for the file name and it needs to be pasted starting at the 4th column.

Hope this makes sense and someone can help me find what I am messing up.

Sub copyStuff()
Dim wb As Workbook, sh As Worksheet, Ssh As Worksheet, fPath As String, fName As String, ffName As String, rng As Range, nm As String, dt As String
fPath = "R:\Patrol Logs\"
If Right(fPath, 1) <> "\" Then fPathe = fPath & "\"
    For Each sh In ThisWorkbook.Sheets 'Sequence through sheets in consolidated workbook.
        nm = sh.Name    'initialize name variable
        For i = 5 To sh.Cells(Rows.Count, 1).End(xlUp).Row
            dt = Format((sh.Cells(i, 1).Value), "mm-dd-yy") 'initialize date variable
            fName = dt & " " & nm & ".xls"  'initialize file name variable
            ffName = fPath & fName
            '---Check whether file exists---
            If Dir(ffName) <> "" Then
                MsgBox (ffName & " exists.")
                Set wb = Workbooks.Open(fPath & fName)  'Open the Officer's file for specified date.
                Set Ssh = wb.Sheets(2) 'Edit sheet name - initialize source sheet variable
                Set rng = Ssh.Range("D1:AO1") 'This will be the range to copy - initialize target data variable
                rng.Copy sh.Cells(i, 4)
                wb.Close
            Else
                'MsgBox (ffName & " does not exist.")
            End If
    'End If
        Next    'increment cell for date - this must complete all dates before sheets change.
    Next    'increment sheet
End Sub

 

[snyper189]

Update: I do not understand the copy function AT ALL. I changed to code to a longer version that does the same thing to see where the problem was occurring. here's the change:

Set swb = Workbooks.Open(ffName)  'Open the Officer's file for specified date.
                Set Ssh = swb.Sheets(2) 'Edit sheet name - initialize source sheet variable
                Ssh.Range("D1:AO1").Copy 'This will be the range to copy - initialize target data variable
                twb.Activate
                sh.Activate
                sh.Range(Cells(i, 4), Cells(i, 42)).Select
                sh.Range(Cells(i, 4), Cells(i, 37)).PasteSpecial
                swb.Close

I went step by step to activate my source and my target destinations. I told it to select where I wanted to put the data. then to paste it in the cell range I, 4 (which I starts at 5 so should be d5) however, when it pastes it there, it still adds 4 rows to my source data. I then changed the range to (I-4, 4) and it copied the correct data, but it placed it 4 rows above where I wanted it. I figured it would place it 4 rows above because of the minues 4 but I can't figure out why specifying the destination with the integer would have any effect on the source data.