Copy data before Query

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Copy data before Query

  1. #1
    New Member
    Join Date
    Mar 2005
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copy data before Query


    Copy data before Query
    I am using Power Query to update a table on the Sheet AllData via an API. I have a separate table on a sheet named Totals with a table tblTotals which has a Total Row. Before my Query refreshes my data, I want to grab my some of the values from the tblTotals Total Row and add it to a third sheet/Table with a timestamp column to log the history. I want this new table to update not overwrite the data. Can this be done with PQ? Can it be done by editing my existing PQ? if so how? I have little to no experience wit PQ but know the mechanics.

    Thanks!

  2. #2
    New Member
    Join Date
    Jun 2015
    Location
    Drammen, Norway
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy data before Query

      
    Hi,

    I don't know if this can be done by PQ, but how about a a few lines of VBA that will copy the info you need, and then update the Query?

    These few lines should work fine:
    Code:
    Sub Copy_and_Update()    
        Dim totalRow As Integer
        Dim lRow As Integer
        
        totalRow = Sheets("Totals").Range("A1").End(xlDown).Row
        lRow = Sheets("History").Range("A1").End(xlDown).Row + 1
        
        Sheets("History").Cells(lRow, 1).Value = Format(Now, "YYYY-MM-DD HH-MM-SS") 'put's a timestamp in the cell below the last row of the "history table"
        Sheets("History").Cells(lRow, 2).Value = Sheets("Totals").Cells(totalRow, 2).Value 'puts the total value from the total sheet, in the cell next to the timestamp
        
        ActiveWorkbook.Connections("Query - AllData").Refresh
    End Sub
    I am guessing your tables starts in A1 in the respective sheets, and that they don't have empty fields?
    Either way I guess you need to modify it for your needs.

    Br.
    Henrik

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com