Copy specific range until row value is specific string

Dweller95

New Member
Joined
Nov 11, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
Hi guys,

I'm trying to find a way to copy a range from and until a cell contains the specific text "Total". L1/2 are the header, so the data should begin copying at L3:Q for the blue background. However, I want to stop copying once it reaches "Total". I'm currently just being thrown errors and have not been able to figure out how to accomplish this on my own, fairly new to VBA.

1636637289067.png


In my Sub Main() I have a While loop that goes through every sheet, activates it and calls addSetB (add blue background data). However, my "addSetB" call throws the error on the rowNum line. "Compile error: Named argument not found." Highlighting "Col:="L" as the error.

While (sht < maxSht)
Sheets(sht).Activate
Call addSetB

' Increment and grab next sheet
sht = sht + 1
Wend

' Dynamically coded as it varies sheet to sheet
' This example it should be grabbing L3:Q16
Sub addSetB()

Dim rowNum As Long
rowNum = Cells.Find(Col:="L", str:="Total", Direction:=xlNext)

' Copy blue background range
Range("L3:Q" & rowNum).Copy

' Activate CIMS sheet and paste in last available blank cell
Sheets("DATA").Activate
Cells(Range("A" & rows.Count).End(xlUp).Row + 1, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False

End Sub
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,395
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
@Dweller95 Am I right in thinking that your bottom rows are merged?
If so, that is likely the source of your issue.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,395
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Then I'm afraid that you will be needing to change it.
Can you unmerge and use Q for Total 100 etc and border format to look as it does now?
 

Dweller95

New Member
Joined
Nov 11, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Then I'm afraid that you will be needing to change it.
Can you unmerge and use Q for Total 100 etc and border format to look as it does now?
Even though the cell appears as "L16" I can't find that value at that specific cell? Or have it stop copy'ing once it reaches the string "Total" in the L column? Preferably it being merged is how I'd like to be able to complete this. I can attempt your suggestion though.
 

Dweller95

New Member
Joined
Nov 11, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
Then I'm afraid that you will be needing to change it.
Can you unmerge and use Q for Total 100 etc and border format to look as it does now?
Unmerging everything and placing "Total" in the Q Column. I still get the error "Named argument cannot be found" using the following code:

Dim rowNum As Long
rowNum = Cells.Find(Col:="Q", str:="Total", Direction:=xlNext)

The section that gets highlighted for the error:
Col:="Q"
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,395
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Try like.
VBA Code:
RowNum = Range("Q:Q").Find("Total", , xlValues, xlWhole).Row
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,395
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Then it sounds as if 'Total' is not there to be found.
Is your string in the last single cell of blue Q 'Total' ? No leading or trailing spaces etc ?
 

Dweller95

New Member
Joined
Nov 11, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
Then it sounds as if 'Total' is not there to be found.
Is your string in the last single cell of blue Q 'Total' ? No leading or trailing spaces etc ?
No extra spaces no. I did however change the function around. It seems to be working as intended using this. It's grabbing the data up to total like I wanted.

It does seem to throw that error sometimes? But most of the time seems to be working, since I keep running and testing it. Not sure how to narrow down the error or remove it completely. But the macro is doing what I intended. Thanks for the help.

VBA Code:
Dim LastRow As Integer
    LastRow = Cells.Find("Total", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row
    Range("L3:Q" & LastRow).Copy
    Sheets("DATA").Activate
    Cells(Range("A" & rows.Count).End(xlUp).Row + 1, 1).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,777
Messages
5,766,411
Members
425,352
Latest member
Jack3h

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