VBA Run-time error 438 appears when “paste” runs

Try Selection.PasteSpecial xlPasteAll Paste by itself works on several objects, most notably Worksheet but not on a Range object which is what your Selection is. To paste to a Range you really have to use the PasteSpecial method with its’ available arguements such as xlPasteAll; xlPasteValues; xlPasteFormulas; xlPasteFormats and others which you can see by … Read more

Delete Hidden/Invisible Rows after Autofilter Excel VBA

So I was kind of looking to get rid of Unfiltered Data rather than trying to reverse all the criteria and delete the visible cells I would use this one: Sub RemoveHiddenRows() Dim oRow As Range, rng As Range Dim myRows As Range With Sheets(“Sheet3”) Set myRows = Intersect(.Range(“A:A”).EntireRow, .UsedRange) If myRows Is Nothing Then … Read more

How to wait until ActiveWorkbook.RefreshAll finishes before executing more code

I had the same issue with an OLEDBConnection connection type, however DoEvents (as suggested in a prior answer) didn’t help me as my data connections had background-refresh enabled. Instead, using Wayne G. Dunn‘s answer as a jumping-off point, I created the following solution, which worked: Sub Refresh_All_Data_Connections() For Each objConnection In ThisWorkbook.Connections ‘Get current background-refresh … Read more