VBA search, copy and paste help

[Giggzz]

Private Sub CommandButton3_Click()
ActiveSheet.Unprotect ("as")
Application.ScreenUpdating = False
Application.EnableEvents = False

'Account number
Worksheets(2).Range("B1").Value = Worksheets("Quote").Range("D19").Value
'names
Worksheets(2).Range("B2").Value = Worksheets("Quote").Range("B5:D5").Value
'address
Worksheets(2).Range("B3").Value = Worksheets("Quote").Range("B6:D6").Value

Application.ScreenUpdating = True
Application.EnableEvents = True
ActiveSheet.Protect ("as")
Unload Me

End Sub
Good morning all, working on a little project and need a little help. Here is what Im looking to do, to create a small data base of account #'s, names, and address's on a seperate sheet.

1. search a column on another sheet - sheet2.range("A") - to see if the value in sheet("Quote").Range("D19") is listed.

2. If the Value is "not" listed then copy Worksheets("Quote").Range("as listed above").Value to Worksheets(2).Range("as listed above").Value to the next empty row.

3. If the searched value in D19 is listed, than have a messgebox state account # already in use.

Im trying to piece some code together so thought I would ask to save some time along the way. Thanks as always.

 

[Datsmart]

If I understand your request, this should work:

Sub CopyUnique()
'Assign Target and Source variables
    TS = Sheets("Sheet2").Name
    SS = Sheets("Quote").Name

    Slastrow = Sheets(SS).UsedRange.Rows.Count
    Tlastrow = Sheets(TS).UsedRange.Rows.Count
    
For i = 1 To Slastrow
    If Not Application.WorksheetFunction.CountIf(Sheets(TS).Range("A:A"), Sheets(SS).Range("A" & i)) = 1 Then
        Sheets(TS).Range("A" & Tlastrow + 1) = Sheets(SS).Range("A" & i)
        Tlastrow = Tlastrow + 1
    Else
        MsgBox "Account #" & vbCrLf _
        & Sheets(SS).Range("A" & i) & vbCrLf _
        & "already in use."
    End If
Next i
End Sub

Note, this code looks at column A not column D, adjust as needed.

 

[Giggzz]

TS = Sheets("Sheet2").Name

getting error - not defined

 

[Giggzz]

Question -

looking at the code - where do I find the reference to cell D19 that supplies the value for the code to look up and compare it to the values listed in the column on Sheet(2). Range("A:A")?? Just trying to understand more about the code.

thanks

 

[Datsmart]

Do you have a worksheet named "Sheet2"?

If not, change the code to your Target worksheet name.

Sub CopyUnique2()
'Assign Target and Source variables
    TS = Sheets("Sheet2").Name
    SS = Sheets("Quote").Name

    Slastrow = Sheets(SS).Range("D65536").End(xlUp).Row
    Tlastrow = Sheets(TS).UsedRange.Rows.Count

For i = 1 To Slastrow
    If Not Application.WorksheetFunction.CountIf(Sheets(TS).Range("A:A"), Sheets(SS).Range("D" & 19 + i)) = 1 Then

        Sheets(TS).Range("A" & Tlastrow + 1) = Sheets(SS).Range("D" & 19 + i)
        Tlastrow = Tlastrow + 1
    Else
        MsgBox "Account #" & vbCrLf _
        & Sheets(SS).Range("D" & 19 + i) & vbCrLf _
        & "already in use."
    End If
Next i
End Sub

I adjusted this code to look at column D on your Quote worksheet.
You still need to change "Sheet2" in the code to your worksheet name.

 

[Giggzz]

TS = Worksheets("List").Name
SS = Worksheets("Quote").Name

These are the names of the sheets and the names in the code. Still getting error - not defined.

 

[Datsmart]

Add this line near the top of your code:

Dim TS$, SS$, Slastrow%, Tlastrow%

 

[Giggzz]

the code is firing now but a few issues.

On Worksheet("Quote"),Range("D19") is the reference cell(value) for the the code to compare to the values list on worksheet("List").Range("A:A") to see if there is a match. If there is no match from the value on worksheet(quote).Range D19 to the worksheet(List), than I want to copy the range below to the next empty row on worksheet(List).

'Account number
Worksheets(2).Range("B1").Value = Worksheets("Quote").Range("D19").Value
'names
Worksheets(2).Range("B2").Value = Worksheets("Quote").Range("B5:D5").Value
'address
Worksheets(2).Range("B3").Value = Worksheets("Quote").Range("B6:D6").Value

Than if there is a match the messagebox stating account already exsists would appear.

 

[Giggzz]

Sub CopyUnique2()
Dim TS$, SS$, Slastrow%, Tlastrow%, i%
'Assign Target and Source variables
TS = Sheets("List").Name
SS = Sheets("Quote").Name

Slastrow = Sheets(SS).Range("D65536").End(xlUp).Row
Tlastrow = Sheets(TS).UsedRange.Rows.Count
For i = 1 To Slastrow
If Not Application.WorksheetFunction.CountIf(Sheets(TS).Range("A:A"), Sheets(SS).Range("D" & 19)) = 1 Then
Sheets(TS).Range("A" & Tlastrow + 1) = Sheets(SS).Range("D" & 19 + i)

Tlastrow = Tlastrow + 1

Else
MsgBox "Account #" & vbCrLf _
& Sheets(SS).Range("D" & 19 + i) & vbCrLf _
& "already in use."
End If
Next i
End Sub

I have tried t modify the code to do what Im looking for but not having much success.