Return the value to the right of the cell

[Romulo Avila]

Good afternoon,
I found it very interesting this VBA code and would like a help instead of seeking a particular cell that he seeks the word "SALE TOTAL" and return the value of the first cell right.

[jolivanes]

One should not hijack someone else's thread. Start your own and refer to this thread if it is relevant.


See if this works (I did not test it)
Change this line

ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = Workbooks(wb).Sheets(i).Range("B32").Value

to this

ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = Workbooks(wb).Sheets(i).UsedRange.Find("SALE TOTAL", , , 1).Offset(, 1).Value

[Romulo Avila]

Jolivanes,

Thanks, did not know this condition, the next time I will open a new thread.

[jolivanes]

No problem. Did the code work for you?

[Romulo Avila]

Jolivanes,


Good afternoon,


You change as you passed and is returning the message.
Msg.JPG

[jolivanes]

Can you attach a clean (no personal info) version of your workbook.
Is your Office an English version?

[Romulo Avila]

Good afternoon,

[SamT]

Sub Copy_From_All_Sheets_In_All_Workbooks()
    Dim wb As String, i As Long
    Dim t
    Dim Found As Range
    Dim Dest As Range
    
    Application.ScreenUpdating = False
    t = Timer
    
    Set Dest = ThisWorkbook.Sheets("Plan1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
    wb = Dir(ThisWorkbook.Path & "\*.xls*")
    Do Until wb = ""
        If wb <> ThisWorkbook.Name Then
          Workbooks.Open ThisWorkbook.Path & "\" & wb
            
            With Workbooks(wb)
              For i = 1 To Workbooks(wb).Sheets.Count
                Set Found = .Sheets(i).UsedRange.Find("SALE TOTAL")
                If Not Found Is Nothing Then
                  Dest.Value = Found.Offset(, 1).Value
                  Set Dest = Dest.Offset(1)
                End If
              Next i
              .Close False
            End With
        End If
        wb = Dir
    Loop
    
    Application.ScreenUpdating = True
    MsgBox "This macro took " & Format(Round(Timer - t, 2), "00:00:00.00") & " seconds to copy" & vbLf & _
    "data from all sheets in all closed workbooks."
End Sub

"Find().Offset" was the problem when item not found