Data pasted into a sheet, with functions referencing those cells, breaks the function

Hello,

A few days ago one of the MVP's here wrote a macro that polled my database and copied the results onto another sheet. The program works nicely.

I then added functions that built off of that data to build a graph from the new data.
1) The original macro won't copy into the cells that are referenced by the new functions I added.
2) When I manually cut/paste the macro's data into the range referenced by my new functions, the functions still do not return to original. I get a #REF! in all the cells.

Can anyone tell me why? And what I can do to fix this?

Thanks.

DBoyd

Hello,

This is the macro that copies data onto another sheet. The attachment button doesn't seem to be working or I would attach the workbook.

Sub ColQ()
    Dim i As Long, LR As Long
    Dim s1 As Worksheet, s2 As Worksheet
    Dim last As Long

    'names s1 and s2 as sheets1 and 2
    Set s1 = Sheets("Pre")
    Set s2 = Sheets("Sheet1")
        LR = s1.Range("Q" & Rows.Count).End(xlUp).Row
    
    With s1
        For i = 1 To LR
        last = s2.Range("B" & Rows.Count).End(xlUp).Row
            If IsNumeric(.Range("Q" & i)) Then
                .Range("M" & i & ":P" & i).Copy
                'paste values into columns B:P on sheet 2
                s2.Range("B" & last + 1).PasteSpecial xlPasteValues
            End If
        Next i
    End With

End Sub

1) I've tried copying the data into tables and into unformatted cells.
The data usually pastes at the end of the previously copied-into range. (even if I delete the previous attempt.) Sometimes it will copy into the beginning of the range but I don't know what I'm doing doing differently when it does.

2) I have IFs using VLOOKUPs,and SUM functions, referencing the copied data.
They all stop working when I manually or automatically paste data into the desired range.

Thanks for the help.

DBoyd