Runtime error 424 object required when adding a button

[Rhodess]

I am still having a problem with my excel macro when I assign it to a button and I can't figure out how to make the sheet active again.

I originally posted a few days ago with

http://www.mrexcel.com/forum/excel-questions/804287-help-excel-macro-not-interpreting-emply-rows.html#post3932860

, but I have had no responses.

Below is the code that is causing the issue:

Private Sub CommandButton1_Click() 
' Transpose xls2qif 
Dim dataRange As Range 
Dim i As Long, j As Long, pointer As Long 
Dim dataArray As Variant, outputArray As Variant, oneVal As Variant   
With ThisWorkbook.Sheets("Convert").Activate
 'Data starts at 12th row and is 11 columns. First row starts with 4th column data     
[COLOR=#ff0000]Set dataRange = Range(.Cells(12, 11), .Cells(.Rows.Count, 4).End(xlUp))[/COLOR] 
End With 
dataArray = dataRange.Value 
ReDim outputArray(1 To dataRange.Rows.Count * dataRange.Columns.Count, 1 To 1) For i = 1 To dataRange.Rows.Count
     For j = 1 To dataRange.Columns.Count
         pointer = pointer + 1
         outputArray(pointer, 1) = dataArray(i, j)
     Next j 
Next i
   ThisWorkbook.Sheets("Output").Range("A1").Resize(UBound(outputArray, 1), 1).Value = outputArray Columns("A:A").ColumnWidth = 27
 End Sub

Originally I had a 1004 error, but I fixed that by adding .Activate to my workbook ("convert"), but now I get a 424 error at the point I highlighted in red.
I tried changing from an array, adding/removing Set but no luck.

Can someone please help, since I'm stumped at this point?

Thanks

 

[Norie]

Remove Activate from the end of the With statement.

With ThisWorkbook.Sheets("Convert")
 'Data starts at 12th row and is 11 columns. First row starts with 4th column data     
    Set dataRange = Range(.Cells(12, 11), .Cells(.Rows.Count, 4).End(xlUp)) 
End With

 

[Rhodess]

When I remove the .Activate, I get the 1004 run time error again in the same location, stating application defined or object defined error

 

[Norie]

Oops, you also need a . before Range.

With ThisWorkbook.Sheets("Convert")
 'Data starts at 12th row and is 11 columns. First row starts with 4th column data     
    Set dataRange = .Range(.Cells(12, 11), .Cells(.Rows.Count, 4).End(xlUp)) 
End With

 

[Rhodess]

Excellent! That did the trick.
Thanks for all your help.

 

[Norie]

No problem, I always forget about those pesky dots.

 

[Rhodess]

I am now getting a 1004 run-time error of my next sheet:

Private Sub CommandButton1_Click()
'Combine Columns
'This macro will clear the contents of column A in "Final", then
'copy the values from "Misc" column A to "Final" column A, and then
'append the values in "Output" column A to the bottom of that list in "Final" column A
Worksheets("Final").Select 'The sheet where you want the results
Range("A:A").ClearContents
Worksheets("Misc").Select 'The sheet where first column data is
[COLOR=#ff0000]Range("A1:" & Range("A65536").End(xlUp).Address).Select[/COLOR]
Selection.Copy
Worksheets("Final").Select 'Go back to paste the data
Range("A1").Select
ActiveSheet.Paste
Worksheets("Output").Select 'the sheet where second column data is
Range("A1:" & Range("A65536").End(xlUp).Address).Select
Selection.Copy
Worksheets("Final").Select 'go back to paste the data
Range("A65536").End(xlUp).Select
Selection.Offset(1, 0).Select
ActiveSheet.Paste
Range("A1").Select
Columns("A:A").ColumnWidth = 27
End Sub

I think it is because its not active from the button press since it works as a macro.
How to I fix this part.

Thanks