Error 438 "Object does not support this property or method

[PGNewbie]

Dim d As Range
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet
    Dim Condition As Worksheet
   
   
   

    Set Source = ActiveWorkbook.Worksheets("Input")
    Set Condition = ActiveWorkbook.Worksheets("Top20NoisiestDevices")
   
   
       'This will start copying data to Target sheet at row 2
   
       
      For Each d In Condition.Range("A2:A21") 'specifiy condition
     
      'create wroksheet for each value in condition
     
      Set Target = Sheets.Add(after:=ActiveSheet)
     
      Target.Name = d.Value
      j = 3
     
        'add code to show client name on line A1
        Sheets("Top20NoisiestDevices")(d.Value).Columns("E").Copy Target.Range("A1") '<--- causes the error  I'm trying to copy the value in column E that corresponds to d.value in the condition sheet
       
       
       
        Sheets("Input").Range("A1:G1").Copy Target.Range("A2")
           
     
     
            For Each c In Source.Range("E2:E6893")
           
           
       
                If Target.Name = d.Value And c.Value = d.Value Then
          
                    Source.Rows(c.Row).Copy Target.Rows(j)
                    j = j + 1
               
               End If
              
            Next c
        Next d

 

[RoryA]

I'm afraid that code and description make no sense to me. Can you elaborate, preferably with an example, what exactly you are trying to copy? If you can post screenshots using the XL2BB tool, that would help.

 

[PGNewbie]

My code creates a new worksheet for each value in column A. I want Row A1 of teach new sheet to display the value of column E. I have over 20 new sheets that are being created

 

[RoryA]

Then replace this:

Sheets("Top20NoisiestDevices")(d.Value).Columns("E").Copy Target.Range("A1")

with this:

Target.Range("A1").Value = d.Offset(, 4).value

 

[PGNewbie]

Then replace this:

Sheets("Top20NoisiestDevices")(d.Value).Columns("E").Copy Target.Range("A1")

with this:

Target.Range("A1").Value = d.Offset(, 4).value

Wow,thank you so much for your help. This worked perfectly Everyone here is so amazing!

 

[RoryA]

Glad to help.