VBA object defined error

AlExcel Mi

New Member
Joined
Jun 15, 2021
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
Hi People,

I'm new to the whole posting situation but I'll explain the matter at hand as best as possible.
SO I have VBA code, this code works GREAT in one workbook as a sub, then when I go copy and go paste from a sub into a Command Button the Code Goes "No." and gives a beautiful object defined error... Oh boy Do I just love those...
So below here is the code part where it starts tripping beautiful error reports...

Private Sub CBExportToSheets_Click()
'start export data
rng4 = Sheet1.Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select

cnt1 = Selection.Rows.Count

MsgBox cnt1

Dim SecRow As Range

For Each SecRow In Selection

'Sheets.Add after:=Sheets(Sheets.Count)
If SecRow <> "" Then
Sheets.Add(after:=Sheets(Sheets.Count)).Name = SecRow.Offset(0, 1) & " " & SecRow
'Sheets.Add.Name = SecRow
''add more things to do here
' PageSet Macro
'

'
Range("A1").Select
ActiveCell.FormulaR1C1 = "SECTION"

-----end of extract-----
You see right there it stops every single time.

I've tried defined names as below

Private Sub CBExportToSheets_Click()
'start export data
'original data range text below
'rng4 = Sheet1.Range("A2").Select
'Range(Selection, Selection.End(xlDown)).Select

'MsgBox "range selection has been named"

'second attempt data range below
'trying to link and use defined name "_rng4"
'doesn't seem to be working either

Set rng4 = ThisWorkbook.Sheets(1).Range("_rng4")

cnt1 = rng4.Rows.Count

MsgBox cnt1

Dim SecRow As Range

For Each SecRow In rng4

'Sheets.Add after:=Sheets(Sheets.Count)
If SecRow <> "" Then
Sheets.Add(after:=Sheets(Sheets.Count)).Name = SecRow.Offset(0, 1) & " " & SecRow
'Sheets.Add.Name = SecRow
MsgBox "Sheet has been named"

' PageSet Macro
'

'This is where I get a problem
'I think it is because of the two active cells
'1 active being my SecRow rng4
'1 being "A1"... on SecRow Sheets

Range("A1").Select
ActiveCell.FormulaR1C1 = "SECTION"

-----end of extract-----
I couldn't get it to work, I ended up with a runtime 9 error message.

What I need it to do is
take the range rng4
count how many rows are in rng4
make new sheets "x" times rows of rng4
rename each sheet as per row of rng4 (referred to as SecRow)
(got it working till here in the command button)
execute the page set up steps on each page
(but this doesn't want to work in the command button.)

I have managed to link my workbooks here should you need them (Files to work with)
The export to sheets copy it the broken command button ("export areas to sheets"),
The export to sheets is a blank command button ("export areas to sheets"),
The print area is the sub code in working condition.
You may need to enable content to see the command buttons.

If you can help I would appreciate it greatly.

Kind regards
Alex
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

AlExcel Mi

New Member
Joined
Jun 15, 2021
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
Good Day people!

It was figured out! Merely add " ActiveSheet. " in front of the range as shown below.

Private Sub CBExportToSheets_Click()
'start export data
rng4 = Sheet1.Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select

cnt1 = Selection.Rows.Count

MsgBox cnt1

Dim SecRow As Range

For Each SecRow In Selection

'Sheets.Add after:=Sheets(Sheets.Count)
If SecRow <> "" Then
Sheets.Add(after:=Sheets(Sheets.Count)).Name = SecRow.Offset(0, 1) & " " & SecRow
'Sheets.Add.Name = SecRow
''add more things to do here
' PageSet Macro
'

'
ActiveSheet.Range("A1").Select
ActiveCell.FormulaR1C1 = "SECTION"

See right there it works now.

Thank you

Kind regards
Alex
 
Solution

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,054
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Glad you figured it out.

One other potential issue (or at the very least, incorrect syntax) is this line here:
VBA Code:
rng4 = Sheet1.Range("A2").Select
You would NOT use a "Select" here. "Select" is for explicitly selecting/activating a cell.

If you just want to store the value from that cell in "rng4", you would just use:
VBA Code:
rng4 = Sheet1.Range("A2").Value

If "rng4" is a range variable, and you want to assign that range to it, you would use:
VBA Code:
Set rng4 = Sheet1.Range("A2")
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,054
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Forum statistics

Threads
1,141,581
Messages
5,707,196
Members
421,497
Latest member
philipg

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