Error 1004 Help. I'm over my head!

[DravenCA]

Hello everyone,



First time poster to the board and fairly new to VBA macrosfor excel. Please bare with me on this post if it is confusing.


I have managed to Frankenstein together code that allows fora worksheet to be created based off of a template sheet, followed by adding anadditional summary line to the summary tab of the workbook.



I have managed to develop a code that was previously workingand now I get "Error 1004: insert method of range failed". I havebeen working on this for a few days now and I’ve hit the “I need help point”.



Below is the codethat I created. I know that I used a lot of ".select" and I believethat I need to remove this to make it run more streamlined.



I’m just not sure how to proceed and really need help withthis.

Private Sub CmdB1_Click()

Dim CC As Variant 'CC = costcenter
Dim Temp As Worksheet
Dim Sum As Worksheet
Dim LastRow As Long
Dim Rplc As Variant

Set Temp = Sheets("Template")
Set Sum = Sheets("Summary")


CC = InputBox("What is the new cost centre number?")
    If CC = vbNullString Then
           'safety code in case an unknown error occurs it will prevent the screen from locking
           Application.ScreenUpdating = True
           Exit Sub
    Else
        Application.ScreenUpdating = False
        Temp.Copy After:=Worksheets(Worksheets.Count)
        'places CC into the proper location of the worksheet & updates tab name
        ActiveSheet.Range("C13").Value = CC
        ActiveSheet.Name = CC
        'Selects summary tab
        Sum.Select
        'sets Rplc to value needed for Find & Replace
        Rplc = Sum.Range("B14").Value
        'Clears out cut&paste memory
        Application.CutCopyMode = False
            'Select Defined name cell Salary
            Range("Salary").Select
            'Copies the required rows
            Range(ActiveCell, ActiveCell.Offset(3, 0)).EntireRow.Select
            Selection.Copy
            'Moves to the end of the row that needs to be used as the paste row
            LastRow = Range("Salary").End(xlDown).Select
            'moves to the begining of the row
            ActiveCell.Offset(1, 0).End(xlToLeft).Select
            'Inserts copied rows into the doc
            Selection.Insert Shift:=xlDown 'Error 1004...
            'Selects newly pasted rows
            Range(ActiveCell, ActiveCell.Offset(3, 0)).EntireRow.Select
            'Displays formulas for find & replace
            ActiveWindow.DisplayFormulas = True
            'Replaced Rplc with the CC
            Selection.Replace What:=Rplc, Replacement:=CC, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False
            'Reverts cells back to display results
            ActiveWindow.DisplayFormulas = False
    End If
End Sub

Thanks for stopping by!

DravenCA

 

[Norie]

Have you checked which row is active when you try and insert?

 

[DravenCA]

The active cell is in the right row at the desired cell.

If I end the macro and hit Insert Paste rows, everything is inserted properly.

 

[DravenCA]

You lead me in the right direction!
I added EntireRow to the code and it worked.

ActiveCell.Offset(1, 0).End(xlToLeft).EntireRow.Select

Thanks for the help!