0

I'm still a big noobie at VBA so any advice would be so appreciated...

I am trying to copy a range from multiple worksheets with a specific name to one worksheet in the same workbook. The range is dynamic in that I know what column the range starts and ends on, but i don't know what row it will start and end on. I have the paste destination figured out, but I need help finding the row the range should start on. The range I want to copy should start after the row in which the cell value = "Open Items:" (i.e. if the row where the cell = "Open Items:" is 3, then i want my range to start at C4)

I'm having an issue with my .Find formula where it's returning nothing. I am confident that the range that I'm searching for "Open Items:" does have a cell that is equal to that. Please see below for my code:

Dim ws As Worksheet
Dim targetws As Worksheet
Dim FindRow As Range
Dim openitemrow As Long

Set targetws = Sheets("targetwsname")

'loop through each ws

For Each ws In ActiveWorkbook.Worksheets

'sets ws name constraint and then tries to find the starting row in the range
If ws.Name Like "*" & "Open" & "*" Then
     Set FindRow = ws.Range("C:C").Find("Open Items:", lookin:=xlValues, lookat:=xlWhole)
     openitemrow = FindRow.Row
'there needs to be values in c4 for me to copy data over but the data i need to copy is in a different location
          If Not IsEmpty(ws.Cells(4, "C")) Then
               ws.Range("C:F" & openitemrow).End(xlUp).Offset(1).Copy Destination:=targetws.Range("D" & Rows.Count).End(xlUp).Offset(1)

          End If
    End If
next ws
bananas
  • 133
  • 8
  • From the `Find` [documentation](https://learn.microsoft.com/en-us/office/vba/api/excel.range.find) The settings for *LookIn*, *LookAt*, *SearchOrder*, and *MatchByte* are saved each time you use this method. ... To avoid problems, set these arguments explicitly each time you use this method. I find that `LookIn` and `LookAt` are more important. – BigBen Aug 11 '21 at 02:46
  • I've added in LookIn for xlValues and LookAt xlWhole earlier since i know what text i'm looking for in the range. I took it out of the code because my .find was still returning nothing. I can add it back in though. – bananas Aug 11 '21 at 02:52
  • @bananas What if you try xlPart? – Raymond Wu Aug 11 '21 at 03:27
  • This is not a proper way to write a Range.... "Range("C:F" & openitemrow)", try looking it up. Check out this SO Question https://stackoverflow.com/questions/12199318/vba-selecting-range-by-variables – GMalc Aug 11 '21 at 04:28
  • I updated it so that it's become ws.Range("C" & openitemstartrow + 1, ws.Range("C" & openitemstartrow + 10).End(xlUp)).Copy targetws.Range("D13", "D" & LastRow).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues didn't realize i could add the "+ 1 " to denote row # changes – bananas Aug 11 '21 at 15:35

1 Answers1

0

If the entire cell value of the cell in question is "Open Items:" and the cell is always in the same column, then you can use match and this will already return it as a number

openitemrow = Application.Match("Open Items:", ws.Range("C:C"), 0)
NelliNoodle
  • 158
  • 1
  • 9
  • That works! Thank you NelliNoodle - i'm now stuck on ws.Range("C:F" & openitemrow).End(xlUp).Offset(1).Copy Destination:=targetws.Range("D" & Rows.Count).End(xlUp).Offset(1) with a "“method range of object _Worksheet failed” error..OpenItemRow is now resulting in 12, but i want my copy to start 1 row after that (aka row 13). The offset(1) should be moving the copy range one row down, right? – bananas Aug 11 '21 at 04:04
  • @bananas This is another question. If NelliNoodle answered your question please give him/her credit by marking it as answered; then ask a new question. – GMalc Aug 11 '21 at 04:19
  • @GMalc thank you for letting me know! I'm new to stackoverflow so i didn't even know there was that function. I will mark it as answered right now. – bananas Aug 11 '21 at 04:45
  • @bananas it's a really simple error there. You can either define a range as C:C or CR:CR. In this instance you have defined it as C:CR. Where C is the column letter and R is the row number. Just pop a row number after the first C and before the : – NelliNoodle Aug 12 '21 at 06:29