Error 1004 Help. I'm over my head!

DravenCA

New Member
Joined
Oct 12, 2018
Messages
3
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.

Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
Have you checked which row is active when you try and insert?
 

DravenCA

New Member
Joined
Oct 12, 2018
Messages
3
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

New Member
Joined
Oct 12, 2018
Messages
3
You lead me in the right direction!
I added EntireRow to the code and it worked.

Code:
ActiveCell.Offset(1, 0).End(xlToLeft).EntireRow.Select
Thanks for the help!
 

Forum statistics

Threads
1,082,358
Messages
5,364,914
Members
400,815
Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top