+ Reply to Thread
Results 1 to 27 of 27

save sheet to another workbook

  1. #1
    Registered User
    Join Date
    07-19-2019
    Location
    Sweden
    MS-Off Ver
    Excel for office 365
    Posts
    35

    save sheet to another workbook

    Hello,

    I was looking into how to save a certain sheet to another workbook. I have managed to do that but only if its opened. then I found the code below for when the receiving workbook is closed.

    My Question:
    is there a way to save to a workbook, being opened or not?
    Also I would like to rename automatically the new sheet with the name from a cell. is that possible?

    Please Login or Register  to view this content.

    thank you in advnace,
    Luis

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: save sheet to another workbook

    Hi Luis

    Welcome to the forum...We will need a bit more information...
    As I understand it...
    You have a open workbook...Within this wb there is a specific sheet that you want copied to a New workbook and renamed according to a value in a cell within this open wb...
    Or do you want to copy it to an existing workbook which is closed?

    Upload a sample file of this workbook depicting the cell containing the new name
    Last edited by sintek; 06-21-2020 at 07:35 AM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Registered User
    Join Date
    07-19-2019
    Location
    Sweden
    MS-Off Ver
    Excel for office 365
    Posts
    35

    Re: save sheet to another workbook

    Hi,

    thank you for the support.

    I have attached some exemples and also an image below:

    Capture.JPG

    I an new to Visual Basic.

    1- What I am after is to copy the info in the workbook_01 ( and main sheet ) to the workbook_02.
    I would like to copy to workbook_02, either with the file open or closed. If the workbook_02 is open, to leave it opened, and if it's closed, to leave it closed.



    2- I would like to copy the name of a specific cell.I managed that with the code below, but the problem is that if the cell has the same name that it will give an error

    Please Login or Register  to view this content.
    thank you in advance,
    Luis
    Attached Files Attached Files

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: save sheet to another workbook

    Assumes WorkBook2 is in same path as WorkBook1


    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-19-2019
    Location
    Sweden
    MS-Off Ver
    Excel for office 365
    Posts
    35

    Re: save sheet to another workbook

    Hi,

    thank you again.

    It works but with some problems:

    Problem 01- it always closes the file, and has I have mentioned, if the file "workbook_02" is opened, I would like to keep it open. If it's closed, I would like to keep it closed.
    Problem 02- if the cell on the mainsheet has the same name, then, it gives an error. is it possible to verify that if the name already exist, to add a number in from to be a unique cell?


    thank you again,
    best regards
    Luis

  6. #6
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: save sheet to another workbook

    If it's closed, I would like to keep it closed.
    Are you wanting code to check to see if the file is open...If it is copy sheet to it and leave it open...If not open the file, copy sheet to it and close it...
    Contradicting perhaps...
    so if Company name of sheets exists then call it company name - 1, 2 , 3 etc

  7. #7
    Registered User
    Join Date
    07-19-2019
    Location
    Sweden
    MS-Off Ver
    Excel for office 365
    Posts
    35

    Re: save sheet to another workbook

    Hi again,

    thank you for the fast reply.

    The ideas was:
    - If I am working with the file "workbook_02" then, I will have it open and would be nice to keep it like that, if I send some info there.
    - If I am not working with the file"workbook_02", then no point on having it opened by the code, so that I have to go and close again.

    I dont know if this is easy or hard to do, or if it makes sense, just thought it was how I wanted the file to work

    For the company name, it could be an idea. If company name exist, to add before the company name a number.

    thank you again,
    best regards,
    Luis

  8. #8
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: save sheet to another workbook

    Okay so this this purpose workBook2 will always be open and once done you will close and save manually???

    Secondly, the numbering of same Company will need to be stored in your workbook somewhere otherwise one will not know what last number is unless you loop every sheet and find the last number used...i.e Apple-1, Apple-2, Apple-3 code must now know to rename Apple-4

    If these are invoices then surely, you will need an incrementing invoice number solution
    What is it you actually are wanting to achieve...save all completed main_sheets in one other file as copies?

  9. #9
    Registered User
    Join Date
    07-19-2019
    Location
    Sweden
    MS-Off Ver
    Excel for office 365
    Posts
    35

    Re: save sheet to another workbook

    Hi,

    please see image below. perhaps its more clear with the image below:

    Capture01.JPG

    for the name, is there any way to code to verify if the name of the sheet already exist and add a unique number to the name of the sheet?

    thank you again,
    best regards,
    Luis

  10. #10
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: save sheet to another workbook

    I am gonna start with the assumption that both books are open and we can add to that later...

    This code will do the following...

    Copy main_sheet to workbook2
    the code then checks to see if Apple Inc exists...
    If not then it renames to Apple Inc
    If it does then it loops to find the last numbered Apple Inc sheet and renames to that 1 incrementing plus 1

    You will end up having ... Apple Inc | Apple Inc-1 | Apple Inc-2 | Apple Inc-3 etc

    Please Login or Register  to view this content.
    Now that this part works...lets get back to the open or close of WorkBook2

    Surely if Workbook2 is closed then you will need code to open it and do the copying and renaming...Is this correct as you cannot do things to a file if it is closed...
    Last edited by sintek; 06-21-2020 at 10:07 AM.

  11. #11
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: save sheet to another workbook

    From everything you've mentioned...I am assuming you want...
    If Wb2 is open, copy and leave it open
    if Wb2 is closed, open, copy and close it again...

  12. #12
    Registered User
    Join Date
    07-19-2019
    Location
    Sweden
    MS-Off Ver
    Excel for office 365
    Posts
    35

    Re: save sheet to another workbook

    Yes

    thank you so much.

    I have chanegd the names from the example file to the one I intend to apply it.

    from: workbook_01.xlsm to: main_file.xlm
    from: workbook_02.xlsm to: Ongoing_Trades.xlsm
    from: main_sheet to: Position_Sizing_Calculator

    I have tried to change in teh code you gave but it an error appeared.


    thank you again,
    best regards,
    Luis

  13. #13
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: save sheet to another workbook

    I have tried to change in teh code you gave but it an error appeared.
    from: workbook_01.xlsm to: main_file.xlm ' missing a s perhaps

    Something is wrong then...cannot say as I do not know what you did...post the code you amended...

    Also in future and Luis this is important....

    Always upload sample files with exact names and exact setup as actual files....

  14. #14
    Registered User
    Join Date
    07-19-2019
    Location
    Sweden
    MS-Off Ver
    Excel for office 365
    Posts
    35

    Re: save sheet to another workbook

    Hi,
    Understood

    I have copied the code you have done and added the names of the file.
    I have also added the name of the cell with the company name "COMPANY_NAME_SIMPLIFIED". (cell is N10 )

    Thank you again

    Please Login or Register  to view this content.

  15. #15
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: save sheet to another workbook

    What error did it give...Cannot see anything wrong...

    Check to make sure the workbook.name is correct and wb1 sheets name is correct

  16. #16
    Registered User
    Join Date
    07-19-2019
    Location
    Sweden
    MS-Off Ver
    Excel for office 365
    Posts
    35

    Re: save sheet to another workbook

    Hi, please find below where the code stops:

    01.JPG

  17. #17
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: save sheet to another workbook

    And......DrumRoll.....the error is ??????

  18. #18
    Registered User
    Join Date
    07-19-2019
    Location
    Sweden
    MS-Off Ver
    Excel for office 365
    Posts
    35

    Re: save sheet to another workbook

    ehehehe sorry

    Run-time error '9':
    Subscription out of range

  19. #19
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: save sheet to another workbook

    add red dot and see
    Please Login or Register  to view this content.

  20. #20
    Registered User
    Join Date
    07-19-2019
    Location
    Sweden
    MS-Off Ver
    Excel for office 365
    Posts
    35

    Re: save sheet to another workbook

    it worked

  21. #21
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: save sheet to another workbook

    I missed that sorry...
    Glad you got it sorted...
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Tx for rep +
    Last edited by sintek; 06-21-2020 at 11:41 AM.

  22. #22
    Registered User
    Join Date
    07-19-2019
    Location
    Sweden
    MS-Off Ver
    Excel for office 365
    Posts
    35

    Re: save sheet to another workbook

    I will most definately

    the only thing that is still not working is if the file is closed. then it doesn't save.

    How do I add to the code that, if the file named is closed, then to open, add the sheet, and close again?

  23. #23
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: save sheet to another workbook

    the only thing that is still not working is if the file is closed. then it doesn't save.
    What are you talking about...if what file is closed...
    If wb2 file is closed then the code will error...

    Look at post 11 and respond with an answer...

  24. #24
    Registered User
    Join Date
    07-19-2019
    Location
    Sweden
    MS-Off Ver
    Excel for office 365
    Posts
    35

    Re: save sheet to another workbook

    Quote Originally Posted by sintek View Post
    From everything you've mentioned...I am assuming you want...
    If Wb2 is open, copy and leave it open
    if Wb2 is closed, open, copy and close it again...
    Yes! if the Wb2 is closed, it should open, add the worksheet from Wb1 and close it again.

  25. #25
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: save sheet to another workbook

    You not very forthcoming in your answers...What about the first part...
    I am assuming answer is yes to both
    Problem 01- it always closes the file, and has I have mentioned, if the file "workbook_02" is opened, I would like to keep it open. If it's closed, I would like to keep it closed.
    Please Login or Register  to view this content.

  26. #26
    Registered User
    Join Date
    07-19-2019
    Location
    Sweden
    MS-Off Ver
    Excel for office 365
    Posts
    35

    Re: save sheet to another workbook

    works perfectly. thank you so much.

    I am very grateful for your time and support

  27. #27
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: save sheet to another workbook

    .........................
    THANKS.gif

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 06-22-2018, 12:07 PM
  2. Save the current worrkbook, save the sheet as .csv and close the workbook
    By SpeediWeb in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-10-2015, 06:50 AM
  3. save .xlsx from active sheet, with sheet name, same address of current workbook
    By Mikeswell in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-31-2014, 12:14 PM
  4. [SOLVED] VBA to save a newly open workbook to a specfic workbook and then sheet within
    By namluke in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-19-2014, 05:14 PM
  5. [SOLVED] Save Active Sheet As New Workbook in same folder of the original workbook
    By Taislin in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-19-2012, 07:03 PM
  6. how to copy excel sheet from one workbook and paste into another workbook, save it ?
    By dearnemo385 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-17-2012, 08:27 AM
  7. Select sheet tabs in workbook & save to separate workbook files
    By stratocaster in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-01-2006, 11:40 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1