copy rows to new sheet and plot graph

[caly0307]

Hi,

I have a data set in excel that i want to sort and copy accross to new tabs. I have created an VBA file than searches the columns for a specified value and copy's over the corresponding row. The program then deletes an rows with an empty cell. The VBA program then plots the resulting sorted data. I want to improve my graph quality by using a between function that only plots values between a certain range. Does anyone know how I would get VBA to do this? I assume it would be an IF function. I also want to put a button on the main sheet that if I press will come up with a message asking to enter the number that I want to copy accros to the new sheets and the button then makes a new tab.

At the moment I have code that copy's and deletes blank rows. I made it from adding in other sections of code I found on this forum and it works quite well. Any help would be much appreaciated!

Option Explicit

Sub MyCopyMacro()


    Dim shtSearch As Worksheet
    Dim shtPaste As Worksheet
    Dim myLastSearchRow As Long
    Dim myCopyRow As Long
    Dim myPasteRow As Long

    Set shtSearch = Sheets("Sheet1")

    Set shtPaste = Sheets("27155-60")

    myPasteRow = 2

    Application.ScreenUpdating = False
    
'   Find last row in column A
    shtSearch.Activate
    myLastSearchRow = shtSearch.Cells(Rows.Count, "A").End(xlUp).Row


'   Loop through all cells in column A
    For myCopyRow = 1 To myLastSearchRow
       If InStr(LCase(shtSearch.Cells(myCopyRow, "A")), "27155-60") > 0 Then
            shtSearch.Rows(myCopyRow).Copy
            shtPaste.Activate
            Cells(myPasteRow, "A").Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
            myPasteRow = myPasteRow + 4
            shtSearch.Activate

        End If
        
    Next myCopyRow
    
    Application.ScreenUpdating = True
    
    Worksheets("27155-60").Range("D1:D" & myLastSearchRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
   
End Sub