VBA Range.Insert Method: Code line makes a space to put new range in

[DocAElstein]

4 Examples using demo code Sub SpreadApartSlipInGetColoured()

Make spreadsheet Area Free by moving all cells to the right, and take the formats from the left.
Make Area Free by moving all cells to the right, and take the formats from the first column in the cells before those that were moved to the right
( If it is chosen to Shift cells to the right ( Shift:=xlShiftToRight ) when making a space to insert new cells, then taking formats from the left is the default if that second optional parameter ( CopyOrigin:= __ ) argument is omitted )

Start by selecting a spreadsheet Area. ( This can be changed later )
Start.JPG : https://imgur.com/JxwZAyC
start code Sub SpreadApartSlipInGetColoured

Select to make available an Area for a new virgin range by Shifting all cells to the right. ( The cells Shifted will start from the left most column of that selection Range )
ShiftCellsToRight.JPG : https://imgur.com/0igT0S1

Select and / or confirm the area to be made free to allow a new range top be inserted
SelectConfirmAreaForNewRange.JPG : https://imgur.com/S2PBtPl

Cells are shifted to the right to make space for a new range Area
CellsShiftedToRightToMakeSpaceForNewRangeArea.JPG : https://imgur.com/ZFnTUd7

Note that the Area originally at the selected Area has also Shifted and consequently has another Address. This is important to note as any variable used to hold the selected Area range object, will, on further use now refer to this new address and not the original.
RangeObjectOfSelectedRangeHasChangedToNewAddress.J PG : https://imgur.com/dlHZ3Q7

Chose that the format for the new cells should come from the cells in the first column to the left of the new range. ( This is the default option when previous Shift is to the right ( Shift:=xlShiftToRight ) )
ChooseToCopyFormatsFromLeft.JPG : https://imgur.com/nmTTUoU

The code pauses, just for fun, to show what would be the full area from which to make an identical Format copy. ( This is the adjacent left mirrored range to the selected Area )
FullFormatCopyRange.JPG : https://imgur.com/vQqWcYr

The actual used single column cells which are used for the format copy and paste are shown
SingleColumnFormatCopyRanges.JPG : https://imgur.com/Y6lz6VX

The copied formats are pasted across all columns
PasteFormatsOverFullNewRangeColumns.JPG : https://imgur.com/MGriOm0

It is intended to reverse all the previous actions in preparation of doing the same with the standard _ Range.Insert code line
NewRangeWillBeDeleted.JPG : https://imgur.com/jR59Cv5

After using the _ Range.Delete Method _ with the appropriate “reverse” options to those used for the Shift and Insert steps so far taken, then we obtain the original spreadsheet as was before any steps were taken.
AfterDeleteWeHaveOriginalSpreadsheet.JPG : https://imgur.com/iJg8zH3

It is intended to use the standard _ Range.Insert code line _ using the Options used previously and still held in variables in the code
Range_InsertCodeLineWillBeUsed.JPG : https://imgur.com/utvAjWz

On performing the standard _ Range.Insert code line _ using the Options used previously ( Shift:=xlShiftToRight, CopyOrigin:=xlFormatFromLeftOrAbove ) and still held in variables in the code, the same Shift and insert results are obtained
SameResultsWithStandardRange_InsertCodeLine.JPG : https://imgur.com/N6brUv6

Finally the demo code presents the actual _ Range.Insert code line _ that would be needed to perform the operation of Shifting and inserting discussed and demonstrated
StandardRange_InsertCodeLineDisplayedInMessageBox : https://imgur.com/ZK2gorS

The Range.Insert code line can also be seem in the Immediate window. This can be erected if one uses the key combination of _ Ctrl+g _ when one is in the VB Editor window environment. ( The VB Editor window can be obtained when the key combination of _ Alt+F11 _ is used from the Excel Spreadsheet environment )
ImmediteWindowDisplaysRequired.JPG : https://imgur.com/8RfsGvQ


Moving cells down to put in a range and taking formats from the next row up
Moving cells down to put in a range and taking formats from the row above the inserted range
( When selecting to Shift cells down for enabling a new range to insert, the default is to take the format from the next row up )

Make a start by selecting a range, ( this can be changed later ), _..
Start.JPG : https://imgur.com/7XFxFpz
_...and start code running Sub SpreadApartSlipInGetColoured

Choose to shift cells down:
ChooseShiftAllRowsDown.JPG : https://imgur.com/DzuP9lB

Confirm selection of the spreadsheet area to be made free by shifting cells
SelectConfirmRange.JPG : https://imgur.com/9ihVp6G

The cells are shifted down ( Including original range selected)
NewRangeAndAllCellsBelowShiftDown.JPG : https://imgur.com/yFsuKGY

Chose to take the format from above
SelectToTakeFormatFromAbove.JPG : https://imgur.com/vuH2csX

Initially the range area above with the same dimensions of the area made free is selected by my code , ( just for fun in this code )
FullCopyRange.JPG : https://imgur.com/jmuBgrF

The required area for format copy is however just one row, that is to say, the current working of the Range.Insert Method, would only use one row in such a case )
ReducedCopyRange.JPG : https://imgur.com/L9Za48F

Those single row formats are pasted across all rows of new Area
PasteRowFormatsAcrossAllNewRange.JPG : https://imgur.com/hcTIU1U

_.........................

The above was all intended to demo what actually goes on in a Range.Insert code line.
The code continues to do an actual Range.Insert code line using the options taken in and used in the above.
NewRangeWillBeDeleted.JPG : https://imgur.com/C2PIquA

Initially a delete is done to reverse the actions done above
BackToStartSituaion.JPG : https://imgur.com/oqpOO4B

The standard single Range.Insert code line is done using the options chosen so far, and the command options used is displayed
StandardCodeLineCommand.JPG : https://imgur.com/rWpuNV2

The information can also be copied from the Immediate window if Keys _ Ctrl+g _ are used when the VB Editor ( Alt+F11) is selected
CommandsDisplayedInImmediateWindow.JPG : https://imgur.com/pMV7HOu

_.________________________________________________ _________________