P101 Copy Specified Sheet Data to Same or Different Excel File (command line example list)

Examples:

Command Line Example 1: Copy all values from one sheet to another, within same workbook

Command Line Example 2: Copy specified range of formula from one workbook to another

Command Line Example 3: Append sheet data from a folder of workbooks to a single sheet

Command Line Example 4: Example 4 Copy ALL including column widths from one workbook to another

P101 Command Line Reference:

This special process can copy the sheet content you specify from one worksheet to another within the same or different workbook. A new Excel file will be created if the output file doesn't exist.

NOTE: If you specify a whole folder of Excel files, you can concatenate/append the data into a single sheet.

Special Parameters Described in Detail

Note: You don't have to specify Boolean (TRUE/FALSE) parameters that are FALSE. Nor do you have to specify parameters that are empty.

/1{Input Sheet(s)} specifies the name or index of the sheet to copy data from. You may specify ranges i.e. "2-4,10" or "*" for all.

/2{What To Copy} is used to specify what to copy from the worksheet. For example to copy Values use /2 0

If this switch is left out, the Values will be copied by default.

/3{bCopySheetName} should be set to "TRUE" if you want the newly created worksheet to have the same name as the input worksheet. bCopySheetName can be true only if you are creating a new workbook by specifying an Output Target File (/T) that is different from the Input File (/S). This is because a Workbook file (XLS) can only have uniquely named sheets. This parameter is typically only used when sInputSheet specifies the sheet by an index number. Multiple input sheets are not possible when setting this parameter to TRUE.

/4{bCopyFromSpecifiedRange} should be set to "TRUE" if you plan on using the sSpecifiedRangeToCopyFrom parameter, otherwise set it to "FALSE". If set to "FALSE" the entire 'Used Ranged' will be copied.

/5{sSpecifiedRangeToCopyFrom} is used to select a portion of a sheet to copy the data from. Typical Excel syntax is acceptable. For example "A1:B10", "J5:K20, Z20", etc. If this parameter is blank, the entire used portion of the worksheet will be copied. See below for more on specifying "used ranges".

/6{sTargetSheet} specifies the name or index of the sheet to copy data to.

/7{bCopyToSpecifiedRange} should be set to "TRUE" if you plan on using the sSpecifiedRangeToCopyTo parameter, otherwise set it to "FALSE". If set to "FALSE", the data will be placed beginning at cell A1.

/8{sSpecifiedRangeToCopyTo} is used to select exactly where to place the copy data to within the output worksheet, sTargetSheet. Typical Excel syntax is acceptable. For example "A1:B10", "J5:K20", etc.

HINT: To specify the location to begin copying the data to (without the needing figure out the destination range) specify a single cell (i.e. "B10"). All the copied data will be placed starting at this cell.

•       If this parameter is blank, the data is copied to the same range as specified by /5 sSpecifiedRangeToCopyFrom.

•       If the range specified is smaller than the copied data's range, the data will be truncated.

•       To copy the data below already existing data, set this parameter to "below". To copy the data to the right of existing data, set this parameter to "right".

/9{bCopyColumnWidths} In addition to copying values, formulas, formats, comments etc, you can also, at the same time specify to copy the column widths. Use /9TRUE to copy column widths, /9FALSE to not. If this switch is left out, it defaults to FALSE.

Additional information on specifying ranges

Multiple ranges can now be specified each with or without a wild card.

Range parameters accept wildcard suffixes that resolve to the appropriate last row at the time the process runs. This avoids having to compute or hardcode the size of your data.

The single-asterisk form B2:B* finds the last non-empty cell in column B by scanning upward from the bottom of the column. Use this when you want exactly the data in column B, regardless of what other columns contain.

The double-asterisk form B2:B** instead uses the sheet's overall used area to determine the end row, then restricts the range to column B. Use this when you are copying column B alongside other columns and need the row counts to align — for example, when column D has data through row 100 but column B only has data through row 50, B2:B** will still return through row 100.

The bare-asterisk form B2:* expands to the worksheet's last used cell, which may include columns to the right of the start. The result could be something like B2:D50 if D is the rightmost used column and 50 is the last used row.

Comma-separated lists are supported and each entry is expanded independently. B2:B*,G2:G* is two ranges, evaluated separately, with no interaction between them.

If the column on the right side of the wildcard differs from the start column (for example, B2:D*), the bottom row is taken from the wildcard column (D), and the resulting range spans from B through D.

If a column referenced by B* or B** is empty below the start row, the result collapses to the start cell alone (for example, B2:B* with an empty column B becomes just B2).