Power Query tables in VBA

Hi Everyone,

I have a table generated by Power query and in my VBA I have below code which is trying to refer "header row".

    With Sheets(1)
        ActiveSheet.ListObjects("Table1").HeaderRowRange.Select
        Set header = Selection.SpecialCells(xlCellTypeVisible)
    End With

or

   With Sheets(1)
        .Range(Cells(1, 1), Cells(1, lastColumn)).Select
        Set header = Selection.SpecialCells(xlCellTypeVisible)
    End With

I tried both codes, but VBA is taking my second row instead of header. When I apply same code to a normal excel sheet which is not a table data, both codes is working perfectly.

PS: The result of this macro is copy pasting some data to another excel sheet. So if its table macro is pasting second row. If its not table macro is pasting first row.)

Do you know what is the reason and what would be a solution for this ?


Thank you very much
Orhan

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

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