run-time error 1004: method 'Range' of object'_Worksheet' failed

boksha

New Member
Joined
Apr 7, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
hihi all
pls I need your help. I am new with VBA and have a code that was done by someone few years ago and now am using this excel.
after i've deleted a row in the table I receive error message from the title of this thread. Macro is supposed to add new row in excell with same content as rows before (just deleted so that new datas can be inserted). it has 3 sheets. only on sheet where I've deleted row (last one) macro is not working. other two are ok. here is code. Green row is where the Debug shows error line.

Option Explicit

Dim analytics As Worksheet
Dim analytics1 As Worksheet
Dim analytics2 As Worksheet
Dim base_row As Range
Dim base_row1 As Range
Dim base_row2 As Range

Sub new_row()

Set analytics = Worksheets("EUR-USD")
Set base_row = analytics.Range("base_row")

'new row insertion
analytics.Activate
base_row.Select

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(-1, 0).Range("A1:AJ1").Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:AJ2"), Type:= _
xlFillDefault
base_row.Offset(-1, 0).Range("A1").Select

With Selection
.Offset(0, 2).ClearContents
.Offset(0, 3).ClearContents
.Offset(0, 4).ClearContents
.Offset(0, 5).ClearContents
.Offset(0, 6).ClearContents
.Offset(0, 9).ClearContents
.Offset(0, 10).ClearContents
.Offset(0, 13).ClearContents
.Offset(0, 14).ClearContents

End With

End Sub


Sub new_row1()

Set analytics1 = Worksheets("other curr")
Set base_row1 = analytics1.Range("base_row1")

'new row insertion
analytics1.Activate
base_row1.Select

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(-1, 0).Range("A1:AJ1").Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:AJ2"), Type:= _
xlFillDefault
base_row1.Offset(-1, 0).Range("A1").Select

With Selection
.Offset(0, 2).ClearContents
.Offset(0, 3).ClearContents
.Offset(0, 4).ClearContents
.Offset(0, 5).ClearContents
.Offset(0, 6).ClearContents
.Offset(0, 7).ClearContents
.Offset(0, 9).ClearContents
.Offset(0, 10).ClearContents
.Offset(0, 12).ClearContents
.Offset(0, 13).ClearContents
.Offset(0, 14).ClearContents

End With

End Sub

Sub new_row2()

Set analytics2 = Worksheets("MM-RSD & REPOs")
Set base_row2 = analytics2.Range("base_row2")

'new row insertion
analytics2.Activate
base_row2.Select

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(-1, 0).Range("A1:AC1").Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:AC2"), Type:= _
xlFillDefault
base_row2.Offset(-1, 0).Range("A1").Select

With Selection
.Offset(0, 2).ClearContents
.Offset(0, 3).ClearContents
.Offset(0, 4).ClearContents
.Offset(0, 5).ClearContents
.Offset(0, 6).ClearContents
.Offset(0, 8).ClearContents

End With

End Sub
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
582
Office Version
  1. 365
Platform
  1. Windows
Are you sure the range name "base_row2" exists in your workbook ?
ie if you go Ctrl+F3 or Formula > Name Manager, does that name appear ?
 
Solution

boksha

New Member
Joined
Apr 7, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
name does appear but value is #REF. I see that for other two sheets (base_row and base_row1) value is there
 

boksha

New Member
Joined
Apr 7, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
thank you Alex you've helped me. now when I've set the range for base_row2 in Name Manager it is working fine
 

Watch MrExcel Video

Forum statistics

Threads
1,129,754
Messages
5,638,170
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top