| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - Marco error .......HELP
Good afternoon all,
I am having a problem converting a marco to a command button. When I run this marco .... TOOLS, MARCO, MACROS..., (name).. RUN , the marco runs correct. When I copy clip the marco to the View Code in the command button, it does not work. Below is a attached file that might be able to explain it better. Please help, or what can I do to fix. Thanks, ALP This is what I have in the accutal MARCO. (this works, when I run the marco) Dim myRange As Range Set myRange = Worksheets("Work").Range("L1") If myRange = 1 Then Range("R4:R36").Select Selection.Copy Sheets("QDATA").Select Range("C4").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveWindow.SmallScroll Down:=1 Sheets("WORK").Select ActiveWorkbook.SAVE Range("A2").End(xlUp).Select ElseIf myRange = 2 Then Range("R4:R36").Select Selection.Copy Sheets("QDATA").Select Range("D4").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveWindow.SmallScroll Down:=1 Sheets("WORK").Select ActiveWorkbook.SAVE Range("A2").End(xlUp).Select This is what happens when I try to set this marco to be on my CommandButton1. (doesnt work) Private Sub CommandButton1_Click() Dim myRange As Range Set myRange = Worksheets("Work").Range("L1") If myRange = 1 Then Range("R4:R36").Select Selection.Copy Sheets("QDATA").Select Range("C4").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveWindow.SmallScroll Down:=1 Sheets("WORK").Select ActiveWorkbook.SAVE Range("A2").End(xlUp).Select ElseIf myRange = 2 Then Range("R4:R36").Select Selection.Copy Sheets("QDATA").Select Range("D4").Select ................ this is what is highlighted as error Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveWindow.SmallScroll Down:=1 Sheets("WORK").Select ActiveWorkbook.SAVE Range("A2").End(xlUp).Select My error message is: Run-Time error '1004': Select Method of Range class failed. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel - Marco error .......HELP
Code that is in a Button_Click can generally only deal with the sheet the
button belongs to. I would bet the button is on Sheet("Work"), because it chokes on a reference to Sheet("QData"). If the macro works as expected from a standard code module, then leave it there. Call the macro to run from the button_click only. Private Sub CommandButton1_Click() Call MyMacro End Sub Mike F "ALP" wrote in message ... Good afternoon all, I am having a problem converting a marco to a command button. When I run this marco .... TOOLS, MARCO, MACROS..., (name).. RUN , the marco runs correct. When I copy clip the marco to the View Code in the command button, it does not work. Below is a attached file that might be able to explain it better. Please help, or what can I do to fix. Thanks, ALP This is what I have in the accutal MARCO. (this works, when I run the marco) Dim myRange As Range Set myRange = Worksheets("Work").Range("L1") If myRange = 1 Then Range("R4:R36").Select Selection.Copy Sheets("QDATA").Select Range("C4").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveWindow.SmallScroll Down:=1 Sheets("WORK").Select ActiveWorkbook.SAVE Range("A2").End(xlUp).Select ElseIf myRange = 2 Then Range("R4:R36").Select Selection.Copy Sheets("QDATA").Select Range("D4").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveWindow.SmallScroll Down:=1 Sheets("WORK").Select ActiveWorkbook.SAVE Range("A2").End(xlUp).Select This is what happens when I try to set this marco to be on my CommandButton1. (doesn't work) Private Sub CommandButton1_Click() Dim myRange As Range Set myRange = Worksheets("Work").Range("L1") If myRange = 1 Then Range("R4:R36").Select Selection.Copy Sheets("QDATA").Select Range("C4").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveWindow.SmallScroll Down:=1 Sheets("WORK").Select ActiveWorkbook.SAVE Range("A2").End(xlUp).Select ElseIf myRange = 2 Then Range("R4:R36").Select Selection.Copy Sheets("QDATA").Select Range("D4").Select ................ this is what is highlighted as error Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveWindow.SmallScroll Down:=1 Sheets("WORK").Select ActiveWorkbook.SAVE Range("A2").End(xlUp).Select My error message is: Run-Time error '1004': Select Method of Range class failed. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel - Marco error .......HELP
Try this...
Private Sub CommandButton1_Click() Dim shtWork As Worksheet Dim shtQData As Worksheet Dim myRange As Range Set shtWork = Worksheets("Work") Set shtQData = Sheets("QDATA") Set myRange = shtWork.Range("L1") If myRange = 1 Then shtWork.Range("R4:R36").Copy shtQData.Range("C4").PasteSpecial Paste:=xlValues shtWork.Range("A1").Select ActiveWorkbook.Save ElseIf myRange = 2 Then shtWork.Range("R4:R36").Copy shtQData.Range("D4").PasteSpecial Paste:=xlValues shtWork.Range("A1").Select ActiveWorkbook.Save End If End Sub The bigest difference is that I have alway explicitly declarde the sheet that I am working with. To make that easy I declared some work sheet objects. The other big change is that I am not selecting as I go. I just slows the code down and moves the cursor around for no good reason. If you want you can even get rid of the last ".select" statements and the cursor will be exactly where the user left it. HTH "ALP" wrote: Good afternoon all, I am having a problem converting a marco to a command button. When I run this marco .... TOOLS, MARCO, MACROS..., (name).. RUN , the marco runs correct. When I copy clip the marco to the View Code in the command button, it does not work. Below is a attached file that might be able to explain it better. Please help, or what can I do to fix. Thanks, ALP This is what I have in the accutal MARCO. (this works, when I run the marco) Dim myRange As Range Set myRange = Worksheets("Work").Range("L1") If myRange = 1 Then Range("R4:R36").Select Selection.Copy Sheets("QDATA").Select Range("C4").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveWindow.SmallScroll Down:=1 Sheets("WORK").Select ActiveWorkbook.SAVE Range("A2").End(xlUp).Select ElseIf myRange = 2 Then Range("R4:R36").Select Selection.Copy Sheets("QDATA").Select Range("D4").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveWindow.SmallScroll Down:=1 Sheets("WORK").Select ActiveWorkbook.SAVE Range("A2").End(xlUp).Select This is what happens when I try to set this marco to be on my CommandButton1. (doesnt work) Private Sub CommandButton1_Click() Dim myRange As Range Set myRange = Worksheets("Work").Range("L1") If myRange = 1 Then Range("R4:R36").Select Selection.Copy Sheets("QDATA").Select Range("C4").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveWindow.SmallScroll Down:=1 Sheets("WORK").Select ActiveWorkbook.SAVE Range("A2").End(xlUp).Select ElseIf myRange = 2 Then Range("R4:R36").Select Selection.Copy Sheets("QDATA").Select Range("D4").Select ................ this is what is highlighted as error Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveWindow.SmallScroll Down:=1 Sheets("WORK").Select ActiveWorkbook.SAVE Range("A2").End(xlUp).Select My error message is: Run-Time error '1004': Select Method of Range class failed. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel - Marco error .......HELP
To elaborate, Mike is exactly correct in that with code that is in sheets you
cant just reference a range. You have to alway refernce the sheet and the range. The code I posted does that. "Jim Thomlinson" wrote: Try this... Private Sub CommandButton1_Click() Dim shtWork As Worksheet Dim shtQData As Worksheet Dim myRange As Range Set shtWork = Worksheets("Work") Set shtQData = Sheets("QDATA") Set myRange = shtWork.Range("L1") If myRange = 1 Then shtWork.Range("R4:R36").Copy shtQData.Range("C4").PasteSpecial Paste:=xlValues shtWork.Range("A1").Select ActiveWorkbook.Save ElseIf myRange = 2 Then shtWork.Range("R4:R36").Copy shtQData.Range("D4").PasteSpecial Paste:=xlValues shtWork.Range("A1").Select ActiveWorkbook.Save End If End Sub The bigest difference is that I have alway explicitly declarde the sheet that I am working with. To make that easy I declared some work sheet objects. The other big change is that I am not selecting as I go. I just slows the code down and moves the cursor around for no good reason. If you want you can even get rid of the last ".select" statements and the cursor will be exactly where the user left it. HTH "ALP" wrote: Good afternoon all, I am having a problem converting a marco to a command button. When I run this marco .... TOOLS, MARCO, MACROS..., (name).. RUN , the marco runs correct. When I copy clip the marco to the View Code in the command button, it does not work. Below is a attached file that might be able to explain it better. Please help, or what can I do to fix. Thanks, ALP This is what I have in the accutal MARCO. (this works, when I run the marco) Dim myRange As Range Set myRange = Worksheets("Work").Range("L1") If myRange = 1 Then Range("R4:R36").Select Selection.Copy Sheets("QDATA").Select Range("C4").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveWindow.SmallScroll Down:=1 Sheets("WORK").Select ActiveWorkbook.SAVE Range("A2").End(xlUp).Select ElseIf myRange = 2 Then Range("R4:R36").Select Selection.Copy Sheets("QDATA").Select Range("D4").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveWindow.SmallScroll Down:=1 Sheets("WORK").Select ActiveWorkbook.SAVE Range("A2").End(xlUp).Select This is what happens when I try to set this marco to be on my CommandButton1. (doesnt work) Private Sub CommandButton1_Click() Dim myRange As Range Set myRange = Worksheets("Work").Range("L1") If myRange = 1 Then Range("R4:R36").Select Selection.Copy Sheets("QDATA").Select Range("C4").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveWindow.SmallScroll Down:=1 Sheets("WORK").Select ActiveWorkbook.SAVE Range("A2").End(xlUp).Select ElseIf myRange = 2 Then Range("R4:R36").Select Selection.Copy Sheets("QDATA").Select Range("D4").Select ................ this is what is highlighted as error Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveWindow.SmallScroll Down:=1 Sheets("WORK").Select ActiveWorkbook.SAVE Range("A2").End(xlUp).Select My error message is: Run-Time error '1004': Select Method of Range class failed. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel - Marco error .......HELP
ALP,
I suggest you forget all the selecting as well as copying and pasting and just use the following. I suggest you assign it to a command button from the Forms toolbar instead. Regards, Greg Sub XYZ() Dim c As Range, rng As Range Dim Arr As Variant Dim ws As Worksheet, ws2 As Worksheet Set ws = Sheets("WORK") Set ws2 = Sheets("QDATA") Set c = ws.Range("L1") Set rng = ws.Range("R4:R36") Arr = rng.Value If c = 1 Then ws2.Range("C4").Resize(32, 1) = Arr ElseIf c = 2 Then ws2.Range("D4").Resize(32, 1) = Arr End If ActiveWorkbook.Save End Sub "ALP" wrote: Good afternoon all, I am having a problem converting a marco to a command button. When I run this marco .... TOOLS, MARCO, MACROS..., (name).. RUN , the marco runs correct. When I copy clip the marco to the View Code in the command button, it does not work. Below is a attached file that might be able to explain it better. Please help, or what can I do to fix. Thanks, ALP This is what I have in the accutal MARCO. (this works, when I run the marco) Dim myRange As Range Set myRange = Worksheets("Work").Range("L1") If myRange = 1 Then Range("R4:R36").Select Selection.Copy Sheets("QDATA").Select Range("C4").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveWindow.SmallScroll Down:=1 Sheets("WORK").Select ActiveWorkbook.SAVE Range("A2").End(xlUp).Select ElseIf myRange = 2 Then Range("R4:R36").Select Selection.Copy Sheets("QDATA").Select Range("D4").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveWindow.SmallScroll Down:=1 Sheets("WORK").Select ActiveWorkbook.SAVE Range("A2").End(xlUp).Select This is what happens when I try to set this marco to be on my CommandButton1. (doesnt work) Private Sub CommandButton1_Click() Dim myRange As Range Set myRange = Worksheets("Work").Range("L1") If myRange = 1 Then Range("R4:R36").Select Selection.Copy Sheets("QDATA").Select Range("C4").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveWindow.SmallScroll Down:=1 Sheets("WORK").Select ActiveWorkbook.SAVE Range("A2").End(xlUp).Select ElseIf myRange = 2 Then Range("R4:R36").Select Selection.Copy Sheets("QDATA").Select Range("D4").Select ................ this is what is highlighted as error Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveWindow.SmallScroll Down:=1 Sheets("WORK").Select ActiveWorkbook.SAVE Range("A2").End(xlUp).Select My error message is: Run-Time error '1004': Select Method of Range class failed. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Marco Exits without error | Excel Discussion (Misc queries) | |||
| Excel Marco for IP Address | Excel Worksheet Functions | |||
| how to use marco in excel | New Users to Excel | |||
| excel Marco question ... need help please | Excel Discussion (Misc queries) | |||
| Excel marco upgrade | Excel Programming | |||
Linear Mode
