Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell Select problem (2003)

    I have been asked to write a piece of code to perform some simple actions to a spreadsheet before it is emailed to a collection point for inclusion in a summary report. Basically the spreadsheet I was provided has 2 tabs. One for data entry and the second is a summary. The summary is the only piece that the end user wants to receive. Her previous attempts at this have required significant instruction to the people filling out the survey on how to copy and paste values.

    I wrote this code to prepare the spreadsheet for mailing and opening the email dialog box but it keeps failing on the cell selection line. I have tried several things and I am sure there is something simple that I am overlooking but this needs to ready today and I am feeling the pressure.

    Does anyone have ideas why this won't work?

    Private Sub CommandButton1_Click()

    Sheets("Send back to IU").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Sheets("05-06 Salaries Worksheet").Select
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
    Application.Dialogs(xlDialogSendMail).Show
    End Sub
    Attached Files Attached Files
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Select problem (2003)

    Move the code to a module and then call the macro from your button.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Cell Select problem (2003)

    Try doing it without all the selecting:

    <pre>Private Sub CommandButton1_Click()
    With Sheets("Send back to IU").Cells
    .Copy
    .PasteSpecial Paste:=xlPasteValues
    End With
    With Application
    .CutCopyMode = False
    .DisplayAlerts = False
    Sheets("05-06 Salaries Worksheet").Delete
    .DisplayAlerts = True
    .Dialogs(xlDialogSendMail).Show
    End With
    End Sub</pre>


    Steve

  4. #4
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Select problem (2003)

    Thanks all!

    I found the problem and got it fixed in time!
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

Posting Permissions

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