Jun 18 2021 04:31 PM
Sub fish() ' ' fish Macro ' ' ActiveCell.Offset(-7, -4).Range("A1:A10").Select Selection.Copy Sheets("Price list").Select ActiveCell.Offset(-4, -2).Range("A1").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True ActiveCell.Select Application.CutCopyMode = False Selection.ListObject.ListRows.Add (4) Sheets("Home").Select Selection.ClearContents End Sub
Jun 19 2021 11:33 AM
@Jalma_fresh_fish , have you created this code by macro recorder?
Take care that the recorded code is using references like ActiveCell.Offset(-7, -4).
In such a statement, -7 and -4 mean that do the next action considering the cell at 7 rows up and 4 columns left of the currently active cell at the time of running the macro. Now if the cell which you selected while you recorded the macro was say H17, then probably you selected the cell D10 at the start of macro recording, so macro recorder inserted the code of ActiveCell.Offset (-7,-4). Its result is that, now each time you run the macro, excel would expect that the active cell is 7 rows 'down' and 4 columns 'right' of the target cell - which i believe you are not doing while running the macro. So say if your active cell while running the macro is A1, then Excel cannot find any cell which is 7 rows above and 4 columns left of cell A1 and shows this error.
Suggest you to first select the target cell on which you want the action and then record the macro.
Don't change cell selection after clicking the record button.
Hope it helps.
Jun 19 2021 11:42 AM