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: 6981

Whenever you have a list of unique key values identifying one file in a directory, but the file name contains other text as well it is possible to use wildcards in order to open the file you want.

Below code snippet will do the trick for you.

Source Code

The function Dir() returns an empty String (""), in case a file could not be identified. Therefore you will need extra handling for this case.

In case more than one file was identified by Dir(), only the first file (whichever the first may be) is returned.

Dim sFound As String
 
sFound = Dir(ActiveWorkbook.Path & "\302113*.xlsm")    'the first one found
If sFound <> "" Then
    Workbooks.Open filename:= ActiveWorkbook.Path & "\" & sFound
End If

Last Updated: 25 July 2015

Hits: 6049

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: 6127