Copy and past VBA

[MrPink1986]

Hi there,

I have created a simple macro to look to copy some data from one sheet to another however I have just noticed that the source data can actually fluctuate as to where it may be so my cell reference of going to AL9 and copy the data in AL9-AL500 is not ideal.

The header in AL9 (or shoud be) is "METHOD" how can I amend this to find that header and copy the data from this cell down.

    Application.ScreenUpdating = False    Sheets("Exception_Report").Select
    Range("AL9").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("AL9:AL1500").Select
    Selection.Copy
    Sheets("Regional Runs Template").Select
    Range("A12").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[MrPink1986]

Also I know that on every variation of the report that "Data Product Name" will be the first value in Column A row 10/11/12 or so - based on this information I know all my headers will be on this row. Is there away to define this as my starting point.
One header I will be using in the row is a header that is contained in two other headers - so I am searching for Cat and before I find this my search of the headers will hit catdog and dogcat first however I am only interested in the third hit - it will always be in this order too.

Any help greatly appreciated.

 

[Fluff]

How about

Sub Mrpink1986()
   Dim Fnd As Range
   With Sheets("Exception_Report")
      Set Fnd = .Range("9:9").Find("Method", , , xlWhole, , , False, , False)
      If Fnd Is Nothing Then
         MsgBox "Not found"
         Exit Sub
      End If
      .Range(Fnd, .Cells(Rows.count, Fnd.Column).End(xlUp)).Copy
      Sheets("Regional Runs Template").Range("A12").PasteSpecial xlPasteValues
   End With
End Sub

 

[MrPink1986]

Thanks for the reply - this kinda works - instead of returning not found could we look to loop through another row or couple of rows to find the string - Range 9:9 did not find it however by changing to 10:10 it worked fine.

How about

Sub Mrpink1986()
   Dim Fnd As Range
   With Sheets("Exception_Report")
      Set Fnd = .Range("9:9").Find("Method", , , xlWhole, , , False, , False)
      If Fnd Is Nothing Then
         MsgBox "Not found"
         Exit Sub
      End If
      .Range(Fnd, .Cells(Rows.count, Fnd.Column).End(xlUp)).Copy
      Sheets("Regional Runs Template").Range("A12").PasteSpecial xlPasteValues
   End With
End Sub

 

[Fluff]

Will "Method" only be found in the header row, wherever that is?

 

[MrPink1986]

Yes the string will only be found in the header row - I do have one instance where it will be found as part three times - the first two times it will find the string as a word in another header but I am only concerned when it is found exactly as in looking to find cat not catdog dogcat.

Will "Method" only be found in the header row, wherever that is?

 

[Fluff]

Ok try

Sub Mrpink1986()
   Dim Fnd As Range
   With Sheets("Exception_Report")
      Set Fnd = .Range("[COLOR=#ff0000]9:12[/COLOR]").Find("Method", , , xlWhole, , , False, , False)
      If Fnd Is Nothing Then
         MsgBox "Not found"
         Exit Sub
      End If
      .Range(Fnd, .Cells(Rows.count, Fnd.Column).End(xlUp)).Copy
      Sheets("Regional Runs Template").Range("A12").PasteSpecial xlPasteValues
   End With
End Sub

This will search in rows 9 to 12, change the part in red to suit

 

[MrPink1986]

Thank you so much - that worked a treat.

Ok try

Sub Mrpink1986()
   Dim Fnd As Range
   With Sheets("Exception_Report")
      Set Fnd = .Range("[COLOR=#ff0000]9:12[/COLOR]").Find("Method", , , xlWhole, , , False, , False)
      If Fnd Is Nothing Then
         MsgBox "Not found"
         Exit Sub
      End If
      .Range(Fnd, .Cells(Rows.count, Fnd.Column).End(xlUp)).Copy
      Sheets("Regional Runs Template").Range("A12").PasteSpecial xlPasteValues
   End With
End Sub

This will search in rows 9 to 12, change the part in red to suit

 

[Fluff]

You're welcome & thanks for the feedback