VBA code to copy range of cells in row based on criteria

[ryanw1]

Good Evening,

I have been searching for some time now for examples online and similar post for what I need with no success. I am here now to ask for assistance.

I need a VBA code that will be run by a command button to copy a specific range of cells in a specific row and paste the cells to a different sheet. This would only be based on if any cells in column D contains "ECS" and are not hidden. Example: D12, D15, D17 are not hidden and contain "ECS". Cell ranges B12:I12, B15:I15, and B17:I17 are all copied and pasted to sheet 2 starting at Cell B2.

All help will be greatly appreciated.

 

[JoeMo]

Are the values in the col D cells returned by formulas in those cells or are they entered manually?

 

[ryanw1]

I am trying to create an order form. The value in col D is existing in the worksheet. The user can check boxes in the worksheet to select the specific row (line items). I need the cells associated with ECS to be copy/pasted to a different sheet that is an "order form".

 

[JoeMo]

I am trying to create an order form. The value in col D is existing in the worksheet. The user can check boxes in the worksheet to select the specific row (line items). I need the cells associated with ECS to be copy/pasted to a different sheet that is an "order form".

Assuming the paste sheet already exists and has a tab name "Sheet2", this should do what you want. Assign the code below to a button on the copy sheet.

Sub CopyIfECS()
Dim R As Range, c As Range, NxRw As Long
Set R = Range("D1:D" & Cells(Rows.Count, "D").End(xlUp).Row)
For Each c In R
    If c.Value = "ECS" Then
        If c.EntireRow.Hidden = False Then
            NxRw = Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "B").End(xlUp).Row + 1
            Range(Cells(c.Row, "B"), Cells(c.Row, "I")).Copy Destination:=Sheets("Sheet2").Range("B" & NxRw)
        End If
    End If
Next c
End Sub

 

[ryanw1]

JoeMo,

This code works great! One additional question, is there a way to add a command to clear the paste-sheet before pasting the data? I've found that if a user uses the form twice, the pasted data will be pasted multiple times.

Thanks in advance for all your help!

 

[JoeMo]

JoeMo,

This code works great! One additional question, is there a way to add a command to clear the paste-sheet before pasting the data? I've found that if a user uses the form twice, the pasted data will be pasted multiple times.

Thanks in advance for all your help!

I don't understand what you want. If there are multiple cells not hidden that contain "ECS", don't you want multiple pastes to Sheet2? That won't happen if the sheet is cleared prior to every paste.

 

[ryanw1]

I don't understand what you want. If there are multiple cells not hidden that contain "ECS", don't you want multiple pastes to Sheet2? That won't happen if the sheet is cleared prior to every paste.

Let me try to better explain. As stated previously, I'm creating an order form. I've got multiple tabs with unique project types and scopes. Each tab has line items that can be selected/de-selected (hidden). Each each selected line item contain ECS needs to be copy/pasted to the vendor order form.

Once the user selects the line items on the project type sheet, the ECS data should only be pasted once to the order form. Currently, if the user needs to generate multiple order forms, they must manually clear the order form sheet of the previous ECS line items. I would like it so prior to pasting new data, the existing data is cleared.

With regards to the macro your provided, how can it manipulated to be used on different sheets. I don't see where it is sheet specific, but I've copy/pasted it to a different sheets, but it only pulls the ECS info from the first sheet I used the code on.

Again, many thanks for your help and patience.

 

[JoeMo]

Let me try to better explain. As stated previously, I'm creating an order form. I've got multiple tabs with unique project types and scopes. Each tab has line items that can be selected/de-selected (hidden). Each each selected line item contain ECS needs to be copy/pasted to the vendor order form.

Once the user selects the line items on the project type sheet, the ECS data should only be pasted once to the order form. Currently, if the user needs to generate multiple order forms, they must manually clear the order form sheet of the previous ECS line items. I would like it so prior to pasting new data, the existing data is cleared.

With regards to the macro your provided, how can it manipulated to be used on different sheets. I don't see where it is sheet specific, but I've copy/pasted it to a different sheets, but it only pulls the ECS info from the first sheet I used the code on.

Again, many thanks for your help and patience.

I need some more info to comply with your new requests.
1. What is the tab name of the order form sheet? In your OP you said it was Sheet2.
2. what range does prior data to be cleared occupy on the order form sheet? Is it B2 to the last occupied row in col I or ....?
3. This is not sheet code - it belongs in a standard module and executes on the active sheet. If you want it to run on other sheets, specify the sheet names, or if its almost all the other sheets, specify the sheet names you do not want it run on.

 

[ryanw1]

I was able to figure out using the code on different sheets. Thanks for your efforts. I still need to figure out how to clear the data on the paste sheet. Can you assist me with that?

 

[JoeMo]

I was able to figure out using the code on different sheets. Thanks for your efforts. I still need to figure out how to clear the data on the paste sheet. Can you assist me with that?

Yes, if you will answer questions 1 & 2 from post #8 .