create a new workbook

  • Hi there. I want to the following steps to be done by vba of excel.


    1. copy ranges of cells in the active worksheet (sheet3)
    2. add a new workbook and paste the copied ranges to sheet1
    3. save the new workbook with the new file name (e.g. date)
    4. close the new workbook and clear the contents the copied ranges of cells in old workbook.


    Can anyone please advise me of the following code and make it more simpler?



    Cheers.

  • Re: create a new workbook



    This lines are confusing:

    Code
    If Range("l1").Value <> "" Then Range("l1").Value = CDate(Date) 
    asdate = Range("l1").Value 'date entered by user


    You say date entered by user, but if a value is put in => the current date is filled in.... just a remark

  • Re: create a new workbook


    Hi there. I've changed a bit the codes as follows upon your advice on the date entry. However every step could be executed but it just could not save the new workbook.



    What did I miss?
    Cheers.

  • Re: create a new workbook


    Try the code below

  • Re: create a new workbook


    Hi Dave. Thanks for your reply.
    I got a problem running the following code.




    Cheers.

  • Re: create a new workbook


    Sorry Dave.


    ####


    .Range("a1:o25").Copy Destination:=Worksheets("sheet1").Range("A1")


    This line was hightlighted And prompted With error. Is .range("a1:025") of old workbook or new workbook?


    ActiveWorkbook.Close SaveChanges:=True, Filename:="c:\assignment\" & fdate


    This line was hightlighted And prompted With error.


    ####


    Cheers.

  • Re: create a new workbook


    Hi there. It seems you code work fine in msoffice 2003 but prompted for error in msoffice xp. Any modification I need to the code?


    Cheers.

  • Re: create a new workbook


    Hi there. I got the following error...


    Error: 2147417848(80010108):
    Automation Error
    Clint interrupt object connection (just similar description)


    When I pressed to debug the code, the following line was hightlighted in yellow:


    .Range("a1:o25").Copy Destination:=Worksheets("sheet1").Range("A1")




    Cheers.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!