Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: small question

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is there anyway to specify what directory my macro copies. I am using a macro to copy a sheet, e-mail it, then delete the sheet. I've ran into a problem now in that when my macro copies the sheet it copies it to a directory where my delete macro won't work.
    I thought it should stay in the directory where the file I had open was in but for some reason my copies end up in an NT folder.
    My delete macro has the code to look into a specific folder:D:docs
    Can I change the code of the copy to:
    Sheet(1).Copy ("D:docs")

    Or will I need to change my delete macro to search for the file?

    Thanks,
    I appreciate the help from everyone at Mr. Excel.

    viper

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Viper,

    I'm not entirely sure what you're asking. When you use the Copy method without using the Before or After arguments that sheet will be copied to a new workbook - right? The workbook won't be saved until you either save it manually or do it through code. Would this not do what you need?

    Sheets(1).Copy
    ActiveWorkbook.SaveAs "C:any folder you wantany name.xls"

    If this isn't what you're talking about then please post your code.

    Regards,
    Dan

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes, your post is what I needed. In my code I just saved it as the name I wanted it to be saved as. I assumed that the file would be saved in the directory that the main file was in. Through me for a loop when my file was saved in a different location. I can specify where the file will be saved at and that should take care of the situation.
    Thanks for your reply.


    I appreciate the help from everyone at Mr. Excel.

    viper

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Viper

    Couldn't you just use:

    Sheet(1).Copy

    and email the sheet, then close without saving?



Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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