I've issue with it Run time error 1004

Occasional Visitor

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 

2 Replies

@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.

I suspect the error is occurring here:
ActiveCell.Offset(-4, -2).Range("A1").Select

Depending on the location of the active cell, it may not be able to select the cell 4 rows up and 2 columns to the left (for example, if the active cell is B2, then that location is outside the bounds of the worksheet).

Can you elaborate on what you're trying to do and how your worksheets are laid out? It appears you are trying to copy the selected cells into a table?