Can't get command button to work

[KateNW]

Hi, totally stuck here. So I record a marcos that does what I want (basically take some data from one worksheet and paste (transposing) onto another spreadsheet) and it works - Here it is:

Sheets("Paste Sheet Here").Select
Range("B4:B8").Select
Selection.Copy
Sheets("Database").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
Sheets("Paste Sheet Here").Select
Range("E12:E44").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Database").Select
Range("F1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
Sheets("Paste Sheet Here").Select
Range("F2:F9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Database").Select
Range("AM1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
Sheets("Paste Sheet Here").Select
Range("A1:G44").Select
Application.CutCopyMode = False
Selection.ClearContents
Selection.ClearFormats
ActiveSheet.Shapes("BBC").Select
Selection.Cut
Sheets("Database").Select
Range("A1").Select

Then I cut and paste the same EXACT thing into a command button and - nothing. I get an error "Run Time Error 1004 Select method of Range Class Failed" at "Range("A1").Select" which seems like a rather simple request. Again, it totally works when I run it as a macros, but I need none excel users to do this, so I need to have it so they just click a button and away it goes. Any help?

Thanks!
Kate

 

[prabby25101981]

Is the command button on a different sheet and the data which you are selecting / copying etc on different sheet? If so, then you'll have to use the exact path of the data. Something like this -

Sheets("ABC").Range("A1").Select

 

[KateNW]

Thanks, but tried that and it doesn't seem to make a difference. I've made sure that everytime I move between sheets, I indicate it in the macros. I really am at a loss here. why would it work as a macros, but not once it's attached to a command button?

Any other ideas?

 

[VoG]

Have you tried adding a button from the Forms toolbar and assigning the macro to it?

 

[KateNW]

Awesome! Don't understand why that worked, but it did! Any suggestion on how to format the button so it's not boring grey?

Ta!

 

[VoG]

You can't change the grey but you can change the caption and make it vivid pink and bold

Command buttons don't get on with selecting things. But it isn't necessary to select. For example your code

Sheets("Paste Sheet Here").Select
    Range("B4:B8").Select
    Selection.Copy
    Sheets("Database").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=True

could be replaced by

Sheets("Paste Sheet Here").Range("B4:B8").Copy
Sheets("Database").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Transpose:=True

 

[Nalani]

KateNW,

If you want to "dress up" your button, you could chose any number of shapes from INSERT > SHAPES. Once placed on your sheet, Right Click and chose Format Shape.
(I kind of like the Bevel.)

From there you can Fill in Color, change Line Color, etc,etc.


Don't forget to assign your macro to it when your done "dressing" it.