Copy Data to Another Excel Worksheet Based on Criteria Using VBA

[FrancisM]

Below is the current code. I got some of it on Google & the other lines from a friend. What it currently does is it copies all the names from a row on ("Referrals") to ("VOC_ASST"). What I am trying to do is have it copy the name in column B if there is "VR" in column M. Past code I posted had the screen flashing, and this is a much cleaner & concise batch of code. What I would like to accomplish is 1) copy only the names that have "VR" in column M, 2) have the copied names go in the next available space. (past code would copy from original row location on ("Referrals") to the same row location on ("VOC_ASST"), 3) Do not copy duplicates unless the name is entered a second time.

Code: Select all

Sub VOC_ASST()
Dim TargetSht As Worksheet, SourceSht As Worksheet, SourceCol As Integer, SourceCells As Range

'If an error occurs skip code to the Err-Hanlder line and the display the error message.
On Error GoTo Err_Handler

'This is the sheet where your copy information from.
Set SourceSht = ThisWorkbook.Sheets("Referrals")

'Name of the sheet where data is to be copied to.
Set TargetSht = ThisWorkbook.Sheets("VOC_ASST")

'This is the cells you will copy data from. This is targeting cells B1 to the last used cell in column B
Set SourceCells = SourceSht.Range("B1:B" & SourceSht.Range("B65536").End(xlUp).Row)

'This is finding the next column available in the target sheet. It assumes dates will be in row 1 and data in row 2 down
If TargetSht.Range("A1").Value = "" Then
    'Cell A1 is blank so the column to put data in will be column #1 (ie A)
    SourceCol = 1
ElseIf TargetSht.Range("IV1").Value <> "" Then
    'Cell IV1 has something in it so we have reached the maximum number of columns we can use in this sheet.
    'Dont paste the data but advise the user.
    MsgBox "There are no more columns available in the sheet " & TargetSht.Name, vbCritical, "No More Data Can Be Copied"
    'stop the macro at this point
    Exit Sub
Else


[FrancisM]

I have inserted the code below after the "Sub VOC_ASST()" & before the "Dim TargetSht As Worksheet, SourceSht As Worksheet, SourceCol As Integer, SourceCells As Range". When I run the code I get "Run time error 1004. Applidcation-Defined error or Object-defined error. I using the wrong code or do I have it in the wrong location? Please advise me.

Code: Select all

ActiveSheet.Range("B1:B").AutoFilter field:=13, Criteria1:="VR"

'Sets the range to be filtered (e.g.B1-B65536) and filters for "VR" in the 13th column "M".

[NoSparks]

Did you ever read what I posted to this thread a couple of weeks ago ?

I suspect not. You have fixed data ranges. Why would you deal with entire columns ?
Which by the way, in Excel 2010, is 1,048,576 rows not 65,536.

[FrancisM]

Thank you. I will go back through all the posts & see what I missed.