How to automatically paste the data from one sheet to another using VBA

[CALEB23]

Hello All.

I created a macro wherein the data captured from SAP and imported via power query will automatically pasted on another sheet of the same worksheet, what happened is that even though the query is updated once I ran the macro it captured the previous data generated from power query. What will be the correct script to capture the correct data?

I appreciate any help you can provide.

PnL sheet - exported data from SAP and link to the worksheet using power query
PnL Bal - sheets wherein the data from PnL sheet be pasted

Here is my sample script.

#If VBA7 Then ' Excel 2010 or later
 
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)
 
#Else ' Excel 2007 or earlier
 
    Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)

   
#End If

Sub PnL_Update()

    Sheets("PnL").Visible = True
    Sheets("PnL").Select
    ActiveWorkbook.RefreshAll

    DoEvents
    Sleep 10000

    Sheets("PnL").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("PnL Bal").Select
    Dim lastrow As Long
    lastrow = Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("A" & lastrow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Sleep 10000
   
    Sheets("PnL Bal").Activate
    Worksheets("PnL").Visible = False

End Sub

 

[Alex Blakenburg]

Have you tried turning off Background refresh on the Power Query query ?
Ref: https://solveandexcel.ca/2020/09/11/enable-background-refresh-property-in-power-query-in-excel/

 

[CALEB23]

Have you tried turning off Background refresh on the Power Query query ?
Ref: https://solveandexcel.ca/2020/09/11/enable-background-refresh-property-in-power-query-in-excel/

Thank you for sharing this Alex, this is a great help.

 

[Peter_SSs]

Here is my sample script.

For the future, when doing so please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[Alex Blakenburg]

You're welcome. Glad we could help.