VBA to find specific text and copy below it

Glasgowsmile

Board Regular
Joined
Apr 14, 2018
Messages
152
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?

Code:
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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,203
Office Version
  1. 365
Platform
  1. Windows
Will that string be in any particular column?
Also will there be any other cell in the file that contains "report output"?
 

Glasgowsmile

Board Regular
Joined
Apr 14, 2018
Messages
152
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:
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:
Code:
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.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,203
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
   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

Board Regular
Joined
Apr 14, 2018
Messages
152

ADVERTISEMENT

How about
Code:
   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"

Code:
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
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,203
Office Version
  1. 365
Platform
  1. Windows
I forgot the brackets, it should be
Code:
         Set wkbSourceBook = Workbooks.Open[COLOR=#ff0000]([/COLOR].SelectedItems(1)[COLOR=#ff0000])[/COLOR]
 

Glasgowsmile

Board Regular
Joined
Apr 14, 2018
Messages
152
I forgot the brackets, it should be
Code:
         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.

Code:
  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
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,203
Office Version
  1. 365
Platform
  1. Windows
You could use
Code:
        wkbCrntWorkBook.Sheets("DlyRateCodes1").Range("A1").Value = ActiveSheet.Range("B13").Value
 

Watch MrExcel Video

Forum statistics

Threads
1,123,133
Messages
5,599,912
Members
414,346
Latest member
mmoose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top