Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Simple Click button Macro Does not Run?

    Can someone please tell me why this code does not work:
    Private Sub CommandButton1_Click()
    Worksheets("summary_Projections").Activate
    Range("E25:HT31").Select
    Selection.Copy
    Worksheets("summary_FTEs").Activate
    Range("A2").Select
    Sheets("summary_FTEs").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
    End Sub

    It gets hung on the "Range("E25:HT31").Select" line.

    THANKS
    Stephen <img src=/S/threadhead.gif border=0 alt=threadhead width=28 height=31>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Simple Click button Macro Does not Run?

    Stephen,

    Set it as a general module macro , and either call it directly or just put Call MAcroName in Private Sub CommandButton1_Click()
    and it will work. Otherwise I think you might need to include the worksheet name in references to any range, as the code for the command button is the sheet that button is attached to. Which sheet did you place the command button on ?

    The easiest solution is just a general macro called by the button. Hope you follow what I mean.

    Andrew

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Click button Macro Does not Run?

    That code would certainly run faster if you don't do all of that unnecessary selecting and activating. The following works from a button click event for me:

    <pre>Private Sub CommandButton1_Click()
    Worksheets("summary_Projections").Range("E25:HT31" ).Copy
    Worksheets("summary_FTEs").Range("A2").PasteSpecia l _
    Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    End Sub
    </pre>


    If you need the sheet you pasted to activated with A2 selected after the macro runs, you could add a couple of lines:

    <pre>Private Sub CommandButton1_Click()
    Worksheets("summary_Projections").Range("E25:HT31" ).Copy
    Worksheets("summary_FTEs").Range("A2").PasteSpecia l _
    Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Worksheets("summary_FTEs").Activate
    Worksheets("summary_FTEs").Range("A2").Select
    End Sub
    </pre>

    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Click button Macro Does not Run?

    OK. Great! I got it working. Now, I have a somewhat related question:
    When you filter a range, select a value in one of the filtered columns, then copy the result and paste it to another area or sheet, you get only the filtered rows. Is there an equivalent way to copy only the Subtotaled rows when you use the Data/Subtotals Command?
    This is not urgent, as I have found a way around it, but I would like to know how to make my work around more general.
    Thanks

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Click button Macro Does not Run?

    I have never used filters, and don't know anything about them. So I can't really help with this. You usually stand a better chance of getting an answer if you start a new thread for a question on a different subject.
    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Click button Macro Does not Run?

    Epic,

    To copy just the subtotal rows only use the "group and outline" area to the left to hide the data and leave just the subtotal (and grand total) lines visible. Then select the subtotal cells you want to copy (probably all of them), then press Ctrl-G to bring up the GoTo Dialog, click on the special button, then choose "visible cells only" and press OK.

    Now click on the copy button or press Ctrl-C, and only the subtotals will be in the clipboard for you to paste where you want.

    Ken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •