Solved Copy a table created by a PQ

[pinarello]

There is no problem to copy with a macro a table created by a Power Query. But what do I have to do so that the Power Query is not copied as well?

 

[pinarello]

Since I do not need the data as a formatted table, the following VBA code is sufficient for me:

Sub Copy_a_PQ_table()

    Sheets("Source").Range("tbl_Source").Copy
    Sheets("Target").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                                              SkipBlanks:=False, Transpose:=False

End Sub

Addendum: I wanted to mark the post as done now, but can't find the function.

 

[pinarello]

Now, as I again had the problem to copy a table created by a power query into another workbook without copying the query as well, I searched again and now also found a suitable solution.

The problem is that when copying the table, the Power Query is also copied, but it is deleted afterwards.

Here is the location of my search: https://stackoverflow.com/questions/44827417/delete-a-query-from-excel-workbook-with-vba

I have created the following example for testing:

Sub Copy_a_Query_Table()

    Dim wb_name_Source As String
    Dim wb_name_Target As String
    
    wb_name_Source = ActiveWorkbook.Name
    
    Workbooks.Add
    wb_name_Target = ActiveWorkbook.Name
  
    Windows(wb_name_Source).Activate
    Range("Query_Table[#All]").Copy
    
    Windows(wb_name_Target).Activate
    
    Range("C5").Select
    ActiveSheet.Paste
    ActiveWorkbook.Queries("Query_of_Query_Table").Delete
    
    Windows(wb_name_Source).Activate
    Application.CutCopyMode = False
    
End Sub

 

[p45cal]

You can do it by not copying the query in the first place:

Sub copypq()
With Range("Query_Table[#All]")
  Workbooks.Add
  ActiveSheet.Range("C5").Resize(.Rows.Count, .Columns.Count).Value = .Value
  'Optional if you want to paste formats:
  '.Copy
  'ActiveSheet.Range("C5").PasteSpecial Paste:=xlPasteFormats

  'separate option to make it into a table:
  'ActiveSheet.ListObjects.Add xlSrcRange, ActiveSheet.Range("C5").Resize(.Rows.Count, .Columns.Count), , xlYes
End With
End Sub

 

[pinarello]

Thanks p45cal,

you can see right away that I don't have that much VBA experience. That's why I was glad to have found a solution at all.
But since not all days are over yet, I don't give up the hope to get also still a somewhat better VBA understanding.