This function copies used range of handed worksheet and replaces it with values only.

Source Code

' @Author - Alexander Bolte
' @ChangeDate - 2014-10-09
' @Description - Copy / Pasting contents of handed Worksheets UsedRange
' as values into the same range.
' @Param trg - a Worksheet object holding data.
' @Remarks -
' UsedRange of worksheet will be overwritten by itself pasting only values and nothing else.
' Formulas will be lost.
Public Function copyPasteWorksheetValues(ByRef trg As Worksheet)
    trg.UsedRange.Copy
    trg.UsedRange.PasteSpecial xlPasteValues
End Function

Last Updated: 25 July 2015

Hits: 6847

Recently a collegue had to import a SharePoint list into Excel for automated processing.

Below you can find the source code that I provided him with as an example.

Source Code

Sub LinkedSharePointList()
 
    ActiveSheet.ListObjects.Add SourceType:=xlSrcExternal, _
 
        Source:=Array(<"URL String">, <"ListNameString">, _
 
        <"{GUIDString}">), LinkSource:=True, Destination:=Range("A1")
 
End Sub

Last Updated: 25 July 2015

Hits: 5922

With this collection of articles I want to build a wiki for myself as well as others.

The goal is to build not only a little framework for Excel VBA, but also a collection of general tips and hints on issues I experienced working with Excel and Visual Basic for Excel.

Once completed, I am guessing it never will be, this will serve me well. Like my wife always says, I am "Mr. Forgetful" and a "Lazy Bum".

Yes dear, I love you too.

And it's true, good programmers are lazy!

Last Updated: 01 June 2014

Hits: 6041