abdelfattah
Well-known Member
- Joined
- May 3, 2019
- Messages
- 1,448
- Office Version
- 2019
- 2010
- Platform
- Windows
I got this code for @DanteAmor . I expect bring data from multiple sheet into sheet TOTAL based on match the headers in sheet TOTAL with the headers in others sheets , but it gives "Object variable or with block not set error" in this line
any help to fix this error ,please?
VBA Code:
lr = sh.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
Code:
Sub through_all_sheets()
Dim sh1 As Worksheet, sh As Worksheet
Dim f As Range
Dim j As Long, lr1 As Long, lr As Long
Set sh1 = Sheets("Total")
For Each sh In Sheets
If sh.Name <> sh1.Name Then
lr1 = sh1.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row + 1
lr = sh.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
For j = 1 To sh.Cells(1, Columns.Count).End(1).Column
Set f = sh1.Rows(1).Find(sh.Cells(1, j), , xlValues, xlWhole, , , False)
If Not f Is Nothing Then
sh1.Cells(lr1, f.Column).Resize(lr).Value = sh.Cells(2, j).Resize(lr).Value
End If
Next
End If
Next
End Sub