VBA object defined error

[AlExcel Mi]

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

 

[AlExcel Mi]

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

 

[Joe4]

Welcome to the Board!

Glad you figured it out.

One other potential issue (or at the very least, incorrect syntax) is this line here:

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:

rng4 = Sheet1.Range("A2").Value

If "rng4" is a range variable, and you want to assign that range to it, you would use:

Set rng4 = Sheet1.Range("A2")

 

[AlExcel Mi]

Hi @Joe4

That is always good to know and keep in mind.

Thank you

 

[Joe4]

You are welcome.