Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hello,
I'm having a little trouble with a command button I've been playing around with, all I want it to do is copy text onto another sheet to specific locations using the destination formatting. I came up with this macro: Macro1 Macro ' Macro recorded 06/03/2007 by ' ' ActiveWindow.SmallScroll ToRight:=-2 Range("A3").Select Selection.Copy Sheets("Sheet2").Select Range("A5").Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Sheets("Sheet1").Select Range("F3").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select Range("A7").Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End Sub which works fine, but when I add this code to a command button object in the sheet I get an error. "Run-time error "1004" Select method of range class failed" The error is with these commands... Sheets("Sheet2").Select Range("A5").Select Can anyone help me out? |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
In order to make your code work, you need to replace lines like this
Sheets("Sheet2").Select With this Sheets("Sheet2").Activate But you don't really need to activate sheets and/or select cells when copying. Per your description, all you want to do is copy text onto another sheet to specific locations using the destination formatting. The code below copies the formula in Sheet1!A1 to Sheet2!A1 and retains the format in Sheet2!A1. Sheets("Sheet2").Range("A1").Formula = Sheets("Sheet1").Range("A1").Formula "Tallan" wrote: Hello, I'm having a little trouble with a command button I've been playing around with, all I want it to do is copy text onto another sheet to specific locations using the destination formatting. I came up with this macro: Macro1 Macro ' Macro recorded 06/03/2007 by ' ' ActiveWindow.SmallScroll ToRight:=-2 Range("A3").Select Selection.Copy Sheets("Sheet2").Select Range("A5").Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Sheets("Sheet1").Select Range("F3").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select Range("A7").Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End Sub which works fine, but when I add this code to a command button object in the sheet I get an error. "Run-time error "1004" Select method of range class failed" The error is with these commands... Sheets("Sheet2").Select Range("A5").Select Can anyone help me out? |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Do you have a "Sheet2" ?
Maybe misspelled or has a space in the name? Works for me without error if I have a Sheet2. Gord Dibben MS Excel MVP On Wed, 7 Mar 2007 05:04:10 -0800, Tallan wrote: Hello, I'm having a little trouble with a command button I've been playing around with, all I want it to do is copy text onto another sheet to specific locations using the destination formatting. I came up with this macro: Macro1 Macro ' Macro recorded 06/03/2007 by ' ' ActiveWindow.SmallScroll ToRight:=-2 Range("A3").Select Selection.Copy Sheets("Sheet2").Select Range("A5").Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Sheets("Sheet1").Select Range("F3").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select Range("A7").Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End Sub which works fine, but when I add this code to a command button object in the sheet I get an error. "Run-time error "1004" Select method of range class failed" The error is with these commands... Sheets("Sheet2").Select Range("A5").Select Can anyone help me out? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Method 'MacroOptions' of object'_Application' failed Runtime Error 1004 | Excel Discussion (Misc queries) | |||
Charts.Add error '1004' Method 'Add' of object 'Sheets' failed | Charts and Charting in Excel | |||
Error: method 'select' of object_worksheet' failed | Excel Discussion (Misc queries) | |||
Dynamic chart generation: Run time Error '1004: Method 'Cells' of object'_Global' fai | Charts and Charting in Excel | |||
runtime error 1004 method range of object '_global failed | Excel Discussion (Misc queries) |