Copy over cell ranges based on criteria in a row

slayer1957

Board Regular
Joined
Jan 9, 2017
Messages
50
Good day,

I am struggling a lot for hours now, got code somehow working but it keeps on pasting over my content.

I have criteria, example L;1;2, if there is an L and/or 1 and/or 2 etc. in the cell in the range it copies data to sheet from the check list to make a custom checklist. Every time it copies it over the current range.

It is suppose to stack underneath one section at a time.

I still need to add a lot of sections but this is the basic part i got thus far.

Can someone assist to solve this riddle.

VBA Code:
Sub CopyOverCriteriaRecords()

Dim CriteriaRow As Range
Dim Criteria As Range
Dim PasteCell As Range

Set CriteriaRow = Sheet14.Range("K3:Y3")
'Copy your data to
For Each Criteria In CriteriaRow
    If Sheet17.Range("A3") = "" Then
        Set PasteCell = Sheet17.Range("A3")
    Else
    Set PasteCell = Sheet17.Range("A2").End(xlDown).Offset(0, 0)
    End If

If Criteria = "L" Then Sheet4.Range("A1:H7").Copy PasteCell

If Criteria = "1" Then Sheet4.Range("A9:H16").Copy PasteCell

If Criteria = "2" Then Sheet4.Range("A17:H31").Copy PasteCell

Next Criteria
      
End Sub

Capture2.JPG
Capture1.JPG
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top