Copy specific range until row value is specific string

[Dweller95]

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.

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

 

[Snakehips]

@Dweller95 Am I right in thinking that your bottom rows are merged?
If so, that is likely the source of your issue.

 

[Dweller95]

@Dweller95 Am I right in thinking that your bottom rows are merged?
If so, that is likely the source of your issue.

That's correct. The "Total" row is merged. However when I click on it, it shows that it is cell "L16".

 

[Snakehips]

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]

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]

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]

Try like.

RowNum = Range("Q:Q").Find("Total", , xlValues, xlWhole).Row

 

[Dweller95]

Try like.

RowNum = Range("Q:Q").Find("Total", , xlValues, xlWhole).Row

Throws me an error 91 saying "object variable or With block variable not set."

 

[Snakehips]

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]

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.

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