__________________________________________________ ______________

Moving cells down to put in a range and taking formats from the next row down
Moving cells down to put in a range and taking formats from the first of the rows that was shifted

A range is selected where a new range should be inserted. ( This can be changed later )
Start.JPG : https://imgur.com/KEchHgI

Chose to move all cells below the selection (and the cells in the selection ), down to make space for the new range
ShiftDown.JPG : https://imgur.com/wjEYmSN

Select and / or confirm the range wanted where a new range should be inserted
SelectConfirmNewShiftRange.JPG : https://imgur.com/lZMC8l7

The cells below and included in the selected area are shifted down
RangesShiftedDown.JPG : https://imgur.com/vmrSonk

Note the area under the selection was also shifted down and so has a new address
SelectedrangeWasAlsoShifted.JPG : https://imgur.com/dMOjoV5

It is chosen to use the formats from below for the newly inserted range
ChoseFormatsfrombelow.JPG : https://imgur.com/9y96aZh

Just for fun, my code initially shows what would be the range to use to copy formats for an exact copy of the original formats: This is actually the range that had been in the area where the new area has been inserted
FullCopyRangeAlsoShiftedSelectedRange.JPG : https://imgur.com/xkY7fh4

The actual range used for the formats to be copied is a single row
ReducedCopyRange.JPG : https://imgur.com/y9cmc3h

The format from the single row is pasted over all rows of the newly inserted range. In this example it means that all cells in the new range have no formatting
PasteFormatOverWholeNewRange.JPG : https://imgur.com/XugnPt5

All of the last actions will be reversed
LastActionsToBeReversed.JPG : https://imgur.com/CbMBcBt
This is achieved by deleting the new range and shifting “back up” the cells from bellow to give finally the original stand
OriginalWorksheetRangeForInsertSelected.JPG https://imgur.com/Lgu6B14

The options used in the demo process are still stored in variables in the code, and these are used in the standard Range.Insert code lime to give the same final results as from the detailed demo process
ResultFromRange_InsertCommand.JPG : https://imgur.com/czpCYYn

The final Range.Insert command line is shown
CommandLine.JPG : https://imgur.com/NPgHo8M

If one is in the VB Editor, then on using the Keys, Ctrl+g, the Immediate window is seen that also contains the single code line
CommandInImmediateWindow.JPG : https://imgur.com/x56Deht


Make spreadsheet Area Free by moving all cells to the right, and take the formats from the right.
Make Area Free by moving all cells to the right, and take the formats from the first column in the cells from the original range that had been in the selected Area but which are, along with many other cells, Shifted to the right

Start by selecting a spreadsheet Area. ( This can be changed later )
Start.JPG : https://imgur.com/o7AoBL4

Select to make available an Area for a new virgin range by shifting all cells to the right, (starting with the left most column of the selection Range )
SelectSpreadCellsToRight.JPG : https://imgur.com/qEAZSNn

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

Cells are Shifted to the right.
Note that the Area which was 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.
NoteAreaPreviouslyInSelectedAreaHasAlsoMoved.JPG : https://imgur.com/XEqOhLC

Chose that the format for the new cells should come from the cells in the first column of the original range object which was in the selected area, but which is now Shifted to a new address
FormatToComeFromRight.JPG : https://imgur.com/czFBesz

The code pauses, just for fun, to show what would be the full area from which to make an identical Format copy. This is also the new Area held by the range which had the same dimensions of the selected area, and which Shifted along with all other cells to the right
FormatFullCopyArea.JPG : https://imgur.com/kdBlkfW

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

The copied formats from a single column are pasted across all columns
FormatsPastedAcrossAllColumns.JPG : https://imgur.com/dC20wcE

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

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.
BackToOriginalSpreadsheet.JPG : https://imgur.com/crSbuXz

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_InsertWillBeUsedWithSameOptions.JPG : https://imgur.com/SDEKdDX

After performing the standard Range.Insert code line using the options used previously and still held in variables in the code, the same Shift and insert results are obtained
ResultsUsingRange_InsertCodeLine.JPG : https://imgur.com/JEpuEm2

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
Range_InsertCodeLineShownInMessageBox.JPG : https://imgur.com/pWIoExG

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 )
Range_InsertCodeLineShownInImmediateWindow.JPG : https://imgur.com/jfiOOnl