Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Save prompt?? (xls 97)

    Hi I have the following macro recorded:

    Sub CopyNum()
    Cells.Select
    Selection.Copy
    Set NewBook = Workbooks.Add
    NewBook.SaveAs FileName:="Workbook " & Format(Date, "dd.mm.yy") ' PROMPT HERE INSTEAD??
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False 'PASTE TO NEWLY SAVED FILE????
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False 'PASTE TO NEWLY SAVED FILE
    Range("A1").Select


    'Closes original file
    Windows("Quarterly Issues (9-30).xls").Activate
    ActiveWorkbook.Save
    ActiveWindow.Close
    With ActiveWindow
    .Top = 82
    .Left = 22
    End With
    End Sub

    I need to modify this so that the user can be prompted with a 'save as' box to choose where to save the file. Then the paste specials should occur in this new sheet that was chosen. How do i modify my above macro to do that for me. Right now it automatically saves the file and then an error occurs because it does not know where to paste the info. Anyone know how to do some of this stuff?

    Thanks
    Ed

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

    Re: Save prompt?? (xls 97)

    See if the discussion in <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=55461&page=&v iew=&sb=&o=&vc=1#Post55461>This</A> thread will help.
    Legare Coleman

Posting Permissions

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