.Select gives Runtime Error 1004 - Select Method of Worksheet Class Failed, or .Copy removes Data

[Rhodess]

I'm havin a problem with the following macro, which works standalone, but not when assigned to a button:

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 immediately get the 1004 run-time error. If I change .Select to .Copy and remove the Selection.Copy below, it appears to work since I see the data flash up, then dissappear and it stops on cell A1

Can someone please take a look and see where I'm messing up?

Thanks

 

[AlphaFrog]

Try this...

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton1_Click()
    [color=green]'Combine Columns[/color]
    [color=green]'This macro will clear the contents of column A in "Final", then[/color]
    [color=green]'copy the values from "Misc" column A to "Final" column A, and then[/color]
    [color=green]'append the values in "Output" column A to the bottom of that list in "Final" column A[/color]
    Application.ScreenUpdating = [color=darkblue]False[/color]
    Worksheets("Final").Range("A:A").ClearContents    [color=green]'The sheet where you want the results[/color]
    [color=darkblue]With[/color] Worksheets("Misc")    [color=green]'The sheet where first column data is[/color]
        .Range("A1", .Range("A65536").End(xlUp)).Copy _
                Destination:=Worksheets("Final").Range("A1")    [color=green]'paste the data[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]With[/color] Worksheets("Output")    [color=green]'the sheet where second column data is[/color]
        .Range("A1", .Range("A65536").End(xlUp)).Copy _
                Destination:=Worksheets("Final").Range("A65536").End(xlUp).Offset(1)    [color=green]'paste the data[/color]
    [color=darkblue]End[/color] With
    Worksheets("Final").Select
    Columns("A:A").ColumnWidth = 27
    Application.ScreenUpdating = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Rhodess]

Thanks AlphaFrog, that worked like a charm.