Power Query tables in VBA

Originally Posted by Norie

Orhan

This works for me to select the header row of a table generated with Power Query.

Dim tbl As ListObject

    Set tbl = Sheet2.ListObjects("Table_0")
    
    tbl.HeaderRowRange.Select

Thank you Norie. But unfortunately, it didnt help still. I am sharing my all code below. Maybe if I kindly ask to review it, you can find the cause ?

Basically, I have two range as header and source. And I am pointing for header the Range(Cells(1,1)) and for source I am pointing Range(Cell(2,1)), but some how VBA is pasting second row as header to the new file

And if I change my Sheet1 with a non-table data. It is working. But I need to adjust this for table, since my source will be a power query.

ActiveWorkbook.Sheets(1).Activate
        
    lastrow = Sheets(1).UsedRange.Rows.Count
    lastColumn = 8
    With Sheets(1)
        .Range(Cells(1, 1), Cells(lastrow, lastColumn)).Select
        Set overtimes = Selection
        overtimes.Sort Key1:=Range("H2"), Order1:=xlAscending
    End With
    With Sheets(1)
        .Range(Cells(1, 1), Cells(1, lastColumn)).Select
        Set header = Selection.SpecialCells(xlCellTypeVisible)
    End With
    With Sheets(1)
        For i = 2 To lastrow
            If .Cells(i, 8).Value <> .Cells(i - 1, 8).Value Then
                StartRow = .Cells(i, 7).Row
            End If
            If .Cells(i, 8).Value <> .Cells(i + 1, 8).Value Then
                EndRow = .Cells(i, 8).Row
                .Range(Cells(StartRow, 1), Cells(EndRow, lastColumn)).Select
            Else
                GoTo 1
            End If
            recepient = .Cells(i, 8).Value
            Manager = .Cells(i, 8).Value
            ID = .Cells(i, 7).Value
            Set Source = Selection.SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
        
            If Source Is Nothing Then
                MsgBox "The source is not a range or the sheet is protected, please correct and try again.", vbOKOnly
                Exit Sub
            End If
        
            With Application
                .ScreenUpdating = False
                .EnableEvents = False
            End With
            
            Set wb = ActiveWorkbook
            Set Dest = Workbooks.Add(xlWBATWorksheet)
        
            header.Copy
            With Dest.Sheets(1)
                .Cells(1, 1).PasteSpecial Paste:=8
                .Cells(1, 1).PasteSpecial Paste:=xlPasteValues
                .Cells(1, 1).PasteSpecial Paste:=xlPasteFormats
                .Cells(1, 1).Select
                Application.CutCopyMode = False
            End With
            Source.Copy
            With Dest.Sheets(1)
                .Cells(2, 1).PasteSpecial Paste:=8
                .Cells(2, 1).PasteSpecial Paste:=xlPasteValues
                .Cells(2, 1).PasteSpecial Paste:=xlPasteFormats
                .Cells(2, 1).Select
                Application.CutCopyMode = False
                ActiveWorkbook.SaveAs "\\ant\dept-eu\PRG10\HR\prg10-eu-hr-services\T2-EMEA-Corp\Slovakia\6. Payroll\Discrepancy Notifications\Generated Excels\" & ID & "_" & Manager & "_Pending codes"
            End With