VB To Copy Excel Sparklines

Question

I am using a Blueprism Process to run a VB Script. It copies a range of excel values and pastes as source formatting. When it runs it does not paste sparklines correctly.

Code:

Dim wb, excel, range As Object

Try

wb = GetWorkbook(Handle, Workbook)
excel = wb.Application

range = excel.Selection
range.PasteSpecial(Paste:=-4163, SkipBlanks:=False, Transpose:=False)
range.PasteSpecial(Paste:=-4122, SkipBlanks:=False, Transpose:=False)

Success = True

Catch e As Exception
Success = False
Message = e.Message
Finally
wb = Nothing
range = Nothing
End Try

Answer 1

Not sure why you make 2 paste special. But Firstly you paste only values, later only format:

xlPasteValues -4163 Values are pasted. xlPasteFormats -4122 Copied source format is pasted.

And you could do this in one line:

xlPasteValuesAndNumberFormats 12 Values and Number formats are pasted.

But if you want to paste as source use this one:

xlPasteAllUsingSourceTheme 13 Everything will be pasted using the source theme.

Source: https://learn.microsoft.com/en-us/office/vba/api/excel.xlpastetype