Dear Sir / Madam,
I calculate some values in Spreadsheet A of a Workbook and copy the values only to spreadsheet B in the same workbook using a macro. On the second or subsequent time in which I use the macro to place the newer data in the same location in spreadsheet B by deleting some cells in rows and columns to the left of where the current data is to be stored the macro immediately shows a run-time error. The cells being removed are blank cells with no formulas or values or formatting. The first time I run the macro it works. Upon running it the second time it immediately opens a message which states “Run-time error 1004 You can’t change part of an array”.
I am running the Excel spreadsheet on Windows 10, Excel 2016.
None of the following work to solve the problem:
I cannot find any GWXL97.DLA file;
Edit Copy and Edit Paste of the highlighted cells into the same location as the cells to be deleted;
Changing the macro to a different group of unformatted empty cells to remove produces the same error message;
The trust settings for macros in Excel are open and macros enabled;
Find & Select, Go to Special and Current Array says that no cells are found;
Home and Clear Button, Clear Formats has no effect;
F2, CTRL +X, select new location CTRL +V, CTRL+Shift + enter have no effect.
Following is the initial code section text where the problem occurs:
Application.Goto Reference:="R5C2" Range("B5:X130").Select Selection.Delete Shift:=xlToLeft Application.Goto Reference:="R5C1543" Range("BGI5:BHI130").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Application.Goto Reference:="Model!R[-2]C[-1538]"
How do I remove the error message or simply bypass it as it has no meaning or use?
Many thanks in advance for your assistance.