VBA to Copy and Paste Rows if Condition

[plastakis]

Hi,

I need prepare offer form. In Sheet 2 i have database with all products and full information about them in row. In sheet1 i have offer(proposal) form. So i trying to make such form, that you can select specific product in Sheet 2 using checkbox and that product with full information which arein same row automatic export in proposal form (under 17 row). Please help me because i searched, but haven't found...

 

[My Aswer Is This]

I see your new to the Forum.

When seeking help it's always best to give specific details.

You said:

I need prepare offer form

Do you mean a sheet? If not what is a Form?
And what is the name of this sheet?​

Like in this case we need both sheet names.

And how do you plan to select item using a Checkbox

You said:

that you can select specific product in Sheet 2 using checkbox

See we need specific details like

Search for what? Where do we find the search for value

What range do we search and what is the name of the search sheet

If you provide specific details like this we may be able to help you.

​

 

[plastakis]

Sorry this misunderstanding.. I am beginner in VBA and in this forum too

I have two sheets:
In sheet ("AL") is product database. It looks like this in A Column - Checkbox, B Column - True/False depends on Checkbox, C column -Product name, from E to K columns other specific information about product like price other specifications. There are 980 products.
In sheet ("Calcualtion") is offer proposal form. It starts from 8 row and going down it has the same columns names as in sheet "AL" from C to K columns.
So I trying to do this: when the products are selected in sheet ("Al") using Checkbox they all have to be copied in Sheet ("Calcucalion") under 8 row by button press.

 

[My Aswer Is This]

So your saying in column A you have a ActiveX Checkbox in every cell?

That must have been a task just doing that.

That would be hard for me to write a script but I'm sure someone else on the Forum may be able to help you.

Did you use some sort of script to enter all these check boxes.
If so I would like to see the script.

 

[plastakis]

Yes for every cell... i made open checkbox for every cell .... it was really long process create for every row checkbox. right now I need just vba which copy checked values from al to calculation sheet.

 

[My Aswer Is This]

I will need to leave this question to someone else on the Forum

To have a checkbox for every row and then want the checkbox to activate a script.
Now if you were willing to double click on a cell in column A to run a script this would be easy.

More then likely your going to need to have a script enter all the checkboxes and the script would then tell the checkbox what to do when clicked on.



This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[plastakis]

i just find one code, but need change some parts. I need change it from coping entire rows to specific cells. I just need that would be coped a,b,c,d,e columns in specific row which meets the criteria.

Sub Button5_Click()

a = Worksheets("Agregatu lentele").Cells(Rows.Count, 2).End(xlUp).Row

For i = 2 To a

If Worksheets("Agregatu lentele").Cells(i, 2).Value = True Then

Worksheets("Agregatu lentele").Rows(i).Copy

Worksheets("SKAICIAVIMAI").Activate

b = Worksheets("SKAICIAVIMAI").Cells(Rows.Count, 1).End(xlUp).Row

Worksheets("SKAICIAVIMAI").Cells(b + 1, 1).Select

ActiveSheet.Paste

Worksheets("Agregatu lentele").Activate

End If




Next

Application.CutCopyMode = False

ThisWorkbook.Worksheets("Agregatu lentele").Cells(1, 1).Select

End Sub

 

[My Aswer Is This]

Try this:

Sub Button5_Click()
'Modified  4/17/2019  9:51:15 AM  EDT
Dim a As Long
Dim b As Long
a = Worksheets("Agregatu lentele").Cells(Rows.Count, 2).End(xlUp).Row
b = Worksheets("SKAICIAVIMAI").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To a
        If Sheets("Agregatu lentele").Cells(i, 2).Value = True Then
            Sheets("Agregatu lentele").Cells(i, 1).Resize(, 5).Copy Sheets("SKAICIAVIMAI").Cells(b, 1)
            b = b + 1
        End If
    Next
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Agregatu lentele").Cells(1, 1).Select
End Sub

 

[My Aswer Is This]

I created a script that would add the CheckBoxes to column A of your sheet. So you would not have to do this manually.

This script adds 10 Checkboxes to column A and links to same cell column B

You can modify the script if you want to add more then 10 if you want.

Sub Add_CheckBox()
'Modified  4/17/2019  2:02:45 PM  EDT
Dim r As Range
Dim x As Long
x = 2 ' Start row 2
For Each r In Cells(2, 1).Resize(10) ' This 10 means you will get 10 checkboxes
ActiveSheet.CheckBoxes.Add(r.Left, r.Top, r.Width, r.Height).Select
    With Selection
        .Value = xlOff
        .LinkedCell = "B" & x
        .Display3DShading = False
    End With
    x = x + 1
Next
End Sub

 

[My Aswer Is This]

Edit