Copy excel range and paste only values (pasteSpecial) with skip blanks in VBScript

12,164

Hi Ranjan Finally some how we got it resolves the right way to use the paste special in vb script is

Selection.PasteSpecial -4163, -4142, True, False

-4163 - for XlPastValues -4142 - for Operation:= xlNone True - For skip Blanks False - Transpose

Share:
12,164
Ranjan
Author by

Ranjan

Updated on June 04, 2022

Comments

  • Ranjan
    Ranjan over 1 year

    I need to copy some range of excel from a sheet of source excel file and paste it to a sheet of a target excel file by skipping the blank cells copied from the source excel file.

    The below code is working fine in VBA, how to write equivalent code in VBScript?

    Selection.Copy
    Sheets("Sheet1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    

    I know the code for paste special with only values is "-4163". Below snippet is working fine in this case,

    sourceWorkbook.Worksheets(1).UsedRange.Copy
    targetWorkbook.Worksheets(1).Range("A1").PasteSpecial -4163
    

    But i need it to paste values and skip blank copied cells (i.e do not want to overwrite or replace with the cells of copied range). How to achieve this??

    Any help will be greatly appreciated