3

I am pretty new to VBA so this might be a bit basic. I have a macro that copies a range of cells from sheet "Working Sheet" and pastes them into the active row in Sheet "FCTC". The macro also inserts new rows so the new pasted information is not written over any existing data. I think my issue is the range copy to active cell reference paste but I am not sure. I have posted the code below. Any help would be greatly appreciated.

`Private Sub CommandButton1_Click()
Application.Goto ActiveWorkbook.Sheets("Working Sheet").Range("A1:AQ7")
    Selection.Copy
    Sheets("FCTC").Select
    ActiveCell.Offset(0, 7).EntireRow.Insert
    ActiveCell.Paste
End Sub

The Macro actually works and inserts the required data and moves the rows down as desired. However I get a

run-time error 1004 "PasteSpecial method of Range class failed"

There is clearly an issue with the structure but I haven't been able to resolve it.

Before I run the Macro

After the macro with the new error message

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Dave Cus
  • 33
  • 6
  • You are copying 7 rows and pasting in 1 row. That's why you are getting this error – Mikku Jul 10 '19 at 06:31
  • Note that `Offset(0, 0)` is completely meaningless. Remove it. Also you might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Jul 10 '19 at 06:40
  • `ActiveCell.EntireRow.Copy ActiveCell.Offset(0, 0).EntireRow` copys **and** pastes it in the same position. After that you again paste it in the same position `ActiveCell.PasteSpecial xlPasteAll`. That is probably not what you wanted. Please clarify what your goal is. – Pᴇʜ Jul 10 '19 at 06:42
  • Hi Peh, Thanks for looking at it, I have cleaned up the code in line with your previous comments (please see amended above) and it is still giving me an error. I have copied a few screen shots to show what I am looking for the function to do. I want to insert a new range of cells and not over write the bold totals. line 14 before insert and line 21 after. When I open the debug it highlights the last line of code "ActiveCell.Paste" I hope this clarifies – Dave Cus Jul 11 '19 at 05:44

1 Answers1

0
  1. Don't use .Select or .Activate (How to avoid using Select in Excel VBA).
  2. Give the red cell a name for example PasteBeforeHere. So the user doesn't need to select this cell manually. If you use named ranges you can access the cell by Range("PasteBeforeHere") easily.
  3. Then insert empty rows first before you copy/paste.

Something like the following should work:

Option Explicit

Private Sub CommandButton1_Click()
    Dim Source As Range
    Set Source = ThisWorkbook.Worksheets("Working Sheet").Range("A1:AQ7")

    Dim InsertBefore As Range
    Set InsertBefore = Selection 'better ThisWorkbook.Worksheets("FCTC").Range("PasteBeforeHere")
    Set InsertBefore = ThisWorkbook.Worksheets("FCTC").Range("PasteBeforeHere")

    'insert empty rows (same amount as source has)
    Application.CutCopyMode = False
    InsertBefore.Resize(RowSize:=Source.Rows.Count).EntireRow.Insert

    'copy from source to destination
    Source.Copy Destination:=InsertBefore.Offset(RowOffset:=-Source.Rows.Count)
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • 1
    Thank you very much it worked a treat. I was looking at the range option but was confused as to how to get it to refer to the one single (moving) cell. So you have shed a great deal of light on many dark subjects. Thanks again – Dave Cus Jul 11 '19 at 07:28