VBA Copy and Paste SQL Data as Values in Excel Spreadsheet Not Pasting

Question

I have written a code which open a worksheet that contains a SQL data connection, I then refresh the data, copy the values and attempt to paste that information into the original spreadsheet.

the data does not paste, however, if i manually step through the code hitting F8 the data pastes - i can't figure out why this is -

if this is an easy question my apologies, I am new to VBA, I asked around my office and searched google - still can't find an answer - here is the code

Sub getdata() ' ' getdata Macro ' ' Keyboard Shortcut: Ctrl+a ' ' make holdings report tab visible and clear the contents

Sheets("Holdings Report").Visible = True
Sheets("Holdings Report").Activate
Range("A2:J65536").Select
Selection.ClearContents

' open the original holdings report, refresh data, copy data

Workbooks.Open "\\hcc-fileprint\sys\Share\Institutional Group\Rebalancing\HCNet Update.xlsm"
Sheets("Sheet1").Activate
Application.Wait Now + TimeValue("00:00:02")
ActiveWorkbook.RefreshAll
Application.Wait Now + TimeValue("00:00:02")
ActiveWorkbook.RefreshAll
Range("A2:J65536").Select
Selection.Copy

'activate the rebalancing spreadsheet paste the values and then hide the tab

ThisWorkbook.Activate
Sheets("Holdings Report").Activate
Range("A2").Select
ActiveSheet.paste

End Sub

Answer 1

Could it be that you're actually not copying anything, because the database query started with RefreshAll is running in the background and has not returned any data by the time you reach Copy?

I see that you're waiting 2 seconds, but that might simply not be long enough, whereas single-stepping gives Excel more time to get the data.

Unfortunately, there doesn't seem to be a specific event that you could react to when RefreshAll has completed, which would be the "right" way of going about it. Perhaps Workbook.SheetChange is fired though; you could try that.

Just a shot in the dark, really, but the copy/paste code itself doesn't seem wrong, and you didn't say that you get any errors.

Answer 2

Need to Modify the paste command to paste the values.

ActiveSheet.PasteSpecial xlPasteValues