VBA to find specific text and copy below it

[Glasgowsmile]

Good Afternoon,

I've got the following code below and instead of doing A1:L500 on the Copy/Paste, I want it to look for the cell that has this "#############REPORT OUTPUT#############" and then copy every cell below it between Columns A:M

How would I go about that?

Sub DailyRatesWkday()   Application.DisplayAlerts = False
   Sheets("DlyRateCodes1").Range("A:M").Clear
   Dim wkbCrntWorkBook As Workbook
   Dim wkbSourceBook As Workbook
   Set wkbCrntWorkBook = ActiveWorkbook
   With Application.FileDialog(msoFileDialogOpen)
      .Filters.Clear
      .Filters.Add "CSV Files", "*.csv"
      .AllowMultiSelect = False
      .Show
      If .SelectedItems.Count > 0 Then
         Workbooks.Open .SelectedItems(1)
         Set wkbSourceBook = ActiveWorkbook
         ActiveSheet.Range("A1:L500").Copy
         wkbCrntWorkBook.Sheets("DlyRateCodes1").Range("A1").PasteSpecial xlPasteValues
         wkbSourceBook.Close False
      End If
   End With
   Worksheets("Directions").Activate
End Sub

 

[Fluff]

Will that string be in any particular column?
Also will there be any other cell in the file that contains "report output"?

 

[Glasgowsmile]

Will that string be in any particular column?
Also will there be any other cell in the file that contains "report output"?

The string will be in Column A each time and no other cells will have that string but there is another solution, which may be easier here.

I've got this code:

=IFERROR(INDEX(DlyRateCodes1!$D$25:$M$455,MATCH($CD5,DlyRateCodes1!$B$25:$B$455,0),MATCH(H$1000,DlyRateCodes1!$D$24:$N$24,0)),"")

This part is giving me trouble:

MATCH(H$1000,DlyRateCodes1!$D$24:$N$24,0)),"")

The data I need to match is usually in that range but the problem is... it could at times be in D22:N22 or up to D28:N28 - so I tried D22:N28 to see if it would work but it doesn't -- just shows up blank.

If I could get that to cover a bigger array area for the matching, then I would need to do the VBA adjustment at all.

 

[Fluff]

How about

   Dim Fnd As Range
   
   If .SelectedItems.Count > 0 Then
     [COLOR=#ff0000] Set wkbSourceBook=Workbooks.Open .SelectedItems(1)
      Set Fnd = Range("A:A").Find("report output", , , xlPart, , , False, , False)
      Range(Fnd.Offset(1), Range("L500")).Copy[/COLOR]
      wkbCrntWorkBook.Sheets("DlyRateCodes1").Range("A1").PasteSpecial xlPasteValues
      wkbSourceBook.Close False
   End If

 

[Glasgowsmile]

How about

   Dim Fnd As Range
   
   If .SelectedItems.Count > 0 Then
     [COLOR=#ff0000] Set wkbSourceBook=Workbooks.Open .SelectedItems(1)
      Set Fnd = Range("A:A").Find("report output", , , xlPart, , , False, , False)
      Range(Fnd.Offset(1), Range("L500")).Copy[/COLOR]
      wkbCrntWorkBook.Sheets("DlyRateCodes1").Range("A1").PasteSpecial xlPasteValues
      wkbSourceBook.Close False
   End If

Good Morning,

So I tried this and now I'm getting an error before even being able to run the code "Argument not optional"

Sub Wkday()   
   Application.DisplayAlerts = False
   Sheets("DlyRateCodes1").Range("A:M").Clear
   Dim Fnd As Range
   Dim wkbCrntWorkBook As Workbook
   Dim wkbSourceBook As Workbook
   Set wkbCrntWorkBook = ActiveWorkbook
   With Application.FileDialog(msoFileDialogOpen)
      .Filters.Clear
      .Filters.Add "CSV Files", "*.csv"
      .AllowMultiSelect = False
      .Show
      If .SelectedItems.Count > 0 Then
      Set wkbSourceBook = Workbooks.Open.SelectedItems(1)
      Set Fnd = Range("A:A").Find("report output", , , xlPart, , , False, , False)
      Range(Fnd.Offset(1), Range("L500")).Copy
      wkbCrntWorkBook.Sheets("DlyRateCodes1").Range("A1").PasteSpecial xlPasteValues
      wkbSourceBook.Close False
   End If
   End With
   Worksheets("Directions").Activate
End Sub

 

[Fluff]

I forgot the brackets, it should be

         Set wkbSourceBook = Workbooks.Open[COLOR=#ff0000]([/COLOR].SelectedItems(1)[COLOR=#ff0000])[/COLOR]

 

[Glasgowsmile]

I forgot the brackets, it should be

         Set wkbSourceBook = Workbooks.Open[COLOR=#ff0000]([/COLOR].SelectedItems(1)[COLOR=#ff0000])[/COLOR]

One last thing.

After it copies/paste that, I actually need it to go back into the previous document and copy cell B13 and paste that in A1 of the final sheet.

Tried this and it works but I'm thinking it could be cleaner.

  If .SelectedItems.Count > 0 Then        
        Set wkbSourceBook = Workbooks.Open(.SelectedItems(1))
        Set Fnd = Range("A:A").Find("report output", , , xlPart, , , False, , False)
        Range(Fnd.Offset(1), Range("L500")).Copy
        wkbCrntWorkBook.Sheets("DlyRateCodes1").Range("A1").PasteSpecial xlPasteValues
        ActiveSheet.Range("B13").Copy
        wkbCrntWorkBook.Sheets("DlyRateCodes1").Range("A1").PasteSpecial xlPasteValues
        wkbSourceBook.Close False
      End If

 

[Fluff]

You could use

        wkbCrntWorkBook.Sheets("DlyRateCodes1").Range("A1").Value = ActiveSheet.Range("B13").Value