Search for String in Column A, then paste expanded Named range, in offset cell from string

Hi I am looking to alter code from Vcoolio the following post:

Find string match in column then paste adjacent cell

Here are the steps I am looking to execute:

1. Look for change in cell value in Sheet5 cell $V$1 (there is a dropdown in this with two options) - at this stage I have attempted make this work via a Worksheet Change
2. Look for first instance of the String "Test Column" in Column A.
3. If value of $V$1 is "Big_Column" (without quotes), Select named range "Big_Column" expand the selection by two additional rows - then copy, OR if value of $V$1 is "Small_Column" (without quotes), Select named range "Small_Column" expand the selection by two additional rows - then copy
4. Paste expanded selection 1 row down and 7 columns to the right of the 'found' string.
5. Look for next instance of "Test Column" in Column A, then repeat steps 2 and 3, till all found, then end.

Any help to get this code working would be much appreciated.

Thanks

Here is my edited code. I have only attempted to paste one of the ranges in this code but would like to do both. It could be done with two separate macros if this is cleaner, activated by the worksheet change macro.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$V$1" Then
        Call PasteFlows
    End If
End Sub

_______________________________

Sub PasteFlows()
 
 Application.ScreenUpdating = False
 
 Dim lRow As Long
 Dim fValue As String
 
 
lRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Sheet1.Range("A1:A" & lRow)
 fValue = "Test Column"
 If cell.Offset(0, 1).Value = fValue Then
 Range("Big_Column").Select
Selection.Resize(Selection.Rows.Count + 2, _
   Selection.Columns.Count).Select
   Selection.Copy
 cell.Offset(1, 7).PasteSpecial xlPasteValues
 End If
NextCell:
Next cell

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Thanks Carim - I have tested this and it works, so thank you! In trying to add some additional functionality it has stopped working.

I have attached the sample file.

I would like to be able to call one of two macros on worksheet change of a dropdown in cell $V$1 (I have named this range "ColumnChange"). I have attempted to do this using a Privatesub but now the original macros don't seem to be working themselves.

It now is intended to run as follows:

1. Look for change in cell value in Sheet5, based on this value run the appropriate macro (They are both identical, they just copy and paste a different range)
2. Paste the copied data as in your macro below.

Thanks in advance...

Quote from Carim

Hello,

Without your sample file ... just a blind guess ...

mainly to avoid using Select / Selection ... you could test following:

Code

Sub PasteFlows()
Dim lRow As Long
Dim c As Range
Application.ScreenUpdating = False
    lRow = Sheet3.Range("A" & Rows.Count).End(xlUp).Row
        For Each c In Sheet3.Range("A1:A" & lRow)
            If c.Offset(0, 1).Value = "Test Column" Then
                With Range("Big_Column")
                    .Resize(.Rows.Count + 2, .Columns.Count).Copy
                    c.Offset(1, 7).PasteSpecial xlPasteValues
                End With
            End If
        Next c
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Display More

Hope this will help

Display More

Thanks again Carim - it seems the data is not being pasted by the target macros. When I select Run Macro independent of the WorksheetChange the target cells do not update?

Thanks for your help!

I'm just a bit puzzled how the original code seemed to work OK but now it isn't.

Do I need to make the paste destination the active cell?

Thanks Carim! That fixed it...

Resolved.