How to Create an Event Change that will Let me copy and paste cell ranges based on the text entered into another cell

[ds9703c]

Hi,

I need some help. I'm trying to write a VBA code that will allow me to copy and paste a range of cells from sheet to another based on the text entered into a cell.

So far I have this but it is not working

[COLOR=#212121][FONT=wf_segoe-ui_normal]Private Sub Worksheet_Change(ByVal Target As Range)[/FONT][/COLOR]
[COLOR=#212121][FONT=wf_segoe-ui_normal]Set Target = Target.Cells(31, 3)[/FONT][/COLOR]
[COLOR=#212121][FONT=wf_segoe-ui_normal]If Not Intersect(Target, Range("C31")) Is Nothing Then[/FONT][/COLOR]
[COLOR=#212121][FONT=wf_segoe-ui_normal]If Taregt.Value = "Farm Manure" Then[/FONT][/COLOR]
[COLOR=#212121][FONT=wf_segoe-ui_normal]Sub CopyPasteToAnotherSheet()[/FONT][/COLOR]
[COLOR=#212121][FONT=wf_segoe-ui_normal]Sheets("DefaultData").Range("D7:D12").Copy[/FONT][/COLOR]
[COLOR=#212121][FONT=wf_segoe-ui_normal]Sheets("AD").Activate[/FONT][/COLOR]
[COLOR=#212121][FONT=wf_segoe-ui_normal]Range("L41:L46").Select[/FONT][/COLOR]
[COLOR=#212121][FONT=wf_segoe-ui_normal]ActiveSheet.Paste[/FONT][/COLOR]
[COLOR=#212121][FONT=wf_segoe-ui_normal]End Sub[/FONT][/COLOR]
[COLOR=#212121][FONT=wf_segoe-ui_normal]End If[/FONT][/COLOR]
[COLOR=#212121][FONT=wf_segoe-ui_normal]End If[/FONT][/COLOR]

I want the cells D7:D12 in the Sheet DefaultData to be copied to cells L41:L46 on the AD sheet
when Farm Manure is entered to cell C31 in the AD sheet

Thanks!

 

[Fluff]

Hi & welcome to the baord
Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("C31")) Is Nothing Then
   If Target.Value = "Farm Manure" Then
      Sheets("DefaultData").Range("D7:D12").Copy Range("L41")
   End If
End If
End Sub

 

[ds9703c]

Hi! thanks for your fast reply

when I try this code it says run-time error '9': Subscript out of range and highlights this line
Sheets("DefaultData").Range("D7:D12").Copy Range("L41")

 

[Fluff]

Do you have a sheet called "DefaultData"?
The spelling must be exactly the same as your sheet name