Copy from a worksheet

DarrenSmith1981 · May 28th 2021

Trying to find a value in source sheet data column then copy a range to dest sheet data & paste the range to destination worksheet offset 1 column over.

It can`t find the Value Electric in both sheets by the looks?

And it seems to not like 2 Finds in the same code?

Is that right? if so how can you overcome it?

I`ve asked MrExcel but no reply today

Copy from a worksheet | MrExcel Message Board

Code

Sub Tippa_Electric_Wires()





Dim wsSource As Worksheet, wsDest As Worksheet

Dim QDetails As ComboBox

Dim i As Long, rng As Long

Dim Addme As Range, RngSource As Range, RngDest As Range





Set wsSource = ThisWorkbook.Worksheets("Part_Group_Items_ List")

Set wsDest = ThisWorkbook.Worksheets("Job Card Master")

Set QDetails = Body_And_Vehicle_Type_Form.Quote_Details



With wsSource.Range("D:D")

Set RngSource = .Find("ELECTRICS", , xlValues, xlPart)

End With



With wsDest

Set RngDest = .Find("ELECTRICS", , xlValues, xlPart)

Set Addme = RngDest.Offset(0, 5)

End With



For i = 0 To QDetails.ListCount - 1

If QDetails.Selected(i) Then



If QDetails_Details.List(i, 1) = ("Boxer") & "" & QDetails_Details.List(i, 4) = ("L2") & "" & QDetails_Details.List(i, 2) = ("Single") Then

wsSource.RngSource("A72:O78").Copy Destination:=Addme

End If

End If



End Sub

Display More

**royUK** · May 28th 2021

Attach an example workbook. It looks like you have a combobox, but why you are dimming it ?

Both finds should work. This is untested

Code

Option Explicit

Sub Tippa_Electric_Wires()
    Dim wsSource As Worksheet, wsDest As Worksheet
    
    Dim QDetails As ComboBox
    Dim i As Long, rng As Long
    Dim Addme As Range, RngSource As Range, RngDest As Range
    
    Set wsSource = ThisWorkbook.Worksheets("Part_Group_Items_ List")
    Set wsDest = ThisWorkbook.Worksheets("Job Card Master")
    Set QDetails = Body_And_Vehicle_Type_Form.Quote_Details
    
    Set RngSource = wsSource.Range("D:D").Find("ELECTRICS", , xlValues, xlPart)
    If RngSource Is Nothing Then
        MsgBox "cell not found"
        Exit Sub
    End If
    
    Set RngDest = wsDest.Find("ELECTRICS", , xlValues, xlPart)
    If RngSource Is Nothing Then
        MsgBox "cell not found"
        Exit Sub
        Else: Set Addme = RngDest.Offset(0, 5)
    End If
    
    For i = 0 To QDetails.ListCount - 1
        If QDetails.Selected(i) Then
            If QDetails_Details.List(i, 1) = ("Boxer") & "" & QDetails_Details.List(i, 4) = ("L2") & "" & QDetails_Details.List(i, 2) = ("Single") Then
                wsSource.RngSource("A72:O78").Copy Destination:=Addme
            End If
        End If
    Next i
    
End Sub

Display More

DarrenSmith1981 · May 28th 2021

This code below says Run Time error 13??

Set QDetails = Body_And_Vehicle_Type_Form.Quote_Details

**royUK** · May 29th 2021

As I said- attach an example workbook

DarrenSmith1981 · Jun 1st 2021

Here you go

Automated Cardworker.xlsm

DarrenSmith1981 · Jun 2nd 2021

Please can somebody help Today

**royUK** · Jun 2nd 2021

When you run the code it is highlighting problems, do you not look at these issues? There's one obvious error which I have corrected. Also a couple more that I have fixed.

Where are the ListBoxes in the code?

Code

Sub Tippa_Electric_Wires()
 Dim wsSource As Worksheet, wsDest As Worksheet
 
 Dim QDetails As ComboBox
 Dim i As Long, rng As Long
 Dim Addme As Range, RngSource As Range, RngDest As Range
 
 Set wsSource = ThisWorkbook.Worksheets("Part_Group_Items_ List")
 Set wsDest = ThisWorkbook.Worksheets("Job Card Master")
 Set QDetails = Body_And_Vehicle_Type_Form.Quote_Details
 
 Set RngSource = wsSource.Range("D:D").Find("ELECTRICS", , xlValues, xlPart)
 If RngSource Is Nothing Then
 MsgBox "cell not found"
 Exit Sub
 End If
 
 Set RngDest = wsDest.Cells.Find("ELECTRICS", , xlValues, xlPart)
' If RngSource Is Nothing Then
If RngDest Is Nothing Then
 MsgBox "cell not found"
 Exit Sub
 Else: Set Addme = RngDest.Offset(0, 1)
 End If
 ''///where are these ListBoxes?
 For i = 0 To QDetails.ListCount - 1
 If QDetails.Selected(i) Then
 If QDetails_Details.List(i, 1) = ("Boxer") & "" & QDetails_Details.List(i, 4) = ("L2") & "" & QDetails_Details.List(i, 2) = ("Single") Then
 wsSource.RngSource("A72:O78").Copy Destination:=Addme
 End If
 End If
 Next i
 
End Sub

Display More

DarrenSmith1981 · Jun 2nd 2021

Hi

This is a combobox

DarrenSmith1981 · Jun 2nd 2021

The Combobox is on the 4th line then set on line 10

This line says QDetials Variable not Defined but as far as i can see it is

If QDetails_Details.List(i, 1) = ("Boxer") & "" & QDetails_Details.List(i, 4) = ("L2") & "" & QDetails_Details.List(i, 2) = ("Single") Then

**royUK** · Jun 2nd 2021

Yes, but where is it. Is it on the UserForm. On Friday I posted

Quote

Attach an example workbook. It looks like you have a combobox, but why you are dimming it ?

You still have provided a reply that helps.

DarrenSmith1981 · Jun 2nd 2021

Its quote detail Combobox on the upper level of the form.

I am dimming it because is on a Module Called "Electric_Wires"

**royUK** · Jun 2nd 2021

That's not how you do it.

You need use something, replace the userform and control names.

Code

UserForm1.ComboBox1.List=

I don't see why you don't put that code in the UserForm module.

DarrenSmith1981 · Jun 2nd 2021

I've added it to the form as you said.

How can you make the below code work taking various combined values of ComboBox?

Seems that except for that it`s working?

Code

For x = 0 To Me.Quote_Details.ListCount - 1
If Me.Quote_Details.Selected(x) Then
If Me.Quote_Details.List(x, 1) = ("Boxer") & "" & Me.Quote_Details.List(i, 4) = ("L2") & "" & Me.Quote_Details.List(i, 2) = ("Single") Then
RngSource("A72:O78").Copy Destination:=Addme
End If
End If
Next x

**royUK** · Jun 2nd 2021

What are you actually trying to do?

Quote_Details is a ListBox. I would think you need to use x not i

Code

  For x = 0 To Me.Quote_Details.ListCount - 1
If Me.Quote_Details.Selected(x) Then
If Me.Quote_Details.List(x, 1) = ("Boxer") & "" & Me.Quote_Details.List(x, 4) = ("L2") & "" & Me.Quote_Details.List(x, 2) = ("Single") Then
RngSource("A72:O78").Copy Destination:=Addme
End If
End If
Next x

DarrenSmith1981 · Jun 3rd 2021

What I am trying to do is below.

Trying to find a value in source sheet data column then copy a range to dest sheet data & paste the range to destination worksheet offset 1 column over.

It`s found by the multiple column choices made on the Listbox.

**royUK** · Jun 3rd 2021

Your code was using two different rows in the ListBox based on x & i. I would think you need to use the same row, i.e. x, the selected row.

DarrenSmith1981 · Jun 3rd 2021

Very sorry but don`t understand the last reply please could you change the code then send it to me?

**royUK** · Jun 3rd 2021

I have done in my previous post #14

DarrenSmith1981 · Jun 4th 2021

This code below says Invalid List Property??

Code

If Me.Quote_Details.List(x, 1) = ("Boxer") & "" & Me.Quote_Details.List(x, 4) = ("L2") & "" & Me.Quote_Details.List(x, 2) = ("Single") Then

**royUK** · Jun 4th 2021

Have you debugged your code?

I can't load the userform because it requires connecting to a folder.

When I remove that code I get a Next without For error in the Initialize event.

I get an error in that line because as I have already said you cannot refer to the ListBox that way.

Even when I fix that error another one occurs.

I've fixed the errors but I have no idea what you expect.

The whole code needs reviewing.

Copy from a worksheet

Participate now!

Share