1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Wanted to create Workbook for Each Worksheet.

Discussion in 'VBA Macros' started by jamesexcel1970, Feb 24, 2017.

  1. jamesexcel1970

    jamesexcel1970 Member

    Messages:
    85
    Hello Experts!

    Tried my level best before posting this question.

    Wanted to create Workbook for Each Worksheet and my book consists of 33 sheets and this will be refreshed weekly basis and need separate them by sheet into a new workbook.

    Thanks.
  2. Monty

    Monty Well-Known Member

    Messages:
    631
    James..Can I request u to upload sample else need to guess and may not suit your requirements.
    Arpanakumar and jamesexcel1970 like this.
  3. jamesexcel1970

    jamesexcel1970 Member

    Messages:
    85
    Hey Champ.

    Sorry again for unable to provide due to data security.
  4. Monty

    Monty Well-Known Member

    Messages:
    631
    James...

    Atleast u can mock up some dummy..
    Arpanakumar and jamesexcel1970 like this.
  5. Monty

    Monty Well-Known Member

    Messages:
    631
    Will be easy to provide solutions for any one in forum.
    jamesexcel1970 likes this.
  6. Monty

    Monty Well-Known Member

    Messages:
    631
    Code (vb):
    Sub Saveas_Workbook()

    'Step 1:  Declare all the variables.
       Dim ws As Worksheet
        Dim wb As Workbook

    'Step 2:  Start the looping through sheets
       For Each ws In ThisWorkbook.Worksheets

    'Step 3:  Create new workbook and save it.
       Set wb = Workbooks.Add
        wb.SaveAs ThisWorkbook.Path & "\" & ws.Name

    'Step 4:  Copy the target sheet to the new workbook
       ws.Copy Before:=wb.Worksheets(1)
        wb.Close SaveChanges:=True

    'Step 5:  Loop back around to the next worksheet
       Next ws
    End sub
    Try this..Hope this is helpful.
    Arpanakumar and jamesexcel1970 like this.
  7. jamesexcel1970

    jamesexcel1970 Member

    Messages:
    85
    Thanks for the replay..Will check and confirm.
    would you be interested to answer few more.
  8. Monty

    Monty Well-Known Member

    Messages:
    631
    Please go ahead...Happy to help.
    Arpanakumar and jamesexcel1970 like this.
  9. jamesexcel1970

    jamesexcel1970 Member

    Messages:
    85
    Still testing.

    One quick question what does this line doing here.

    ThisWorkbook.Path
  10. Monty

    Monty Well-Known Member

    Messages:
    631
    James

    Actveworkbook depends on which workbook is active with the path.
    Will save all newly added workbook in the path where actveworkbook is existing.

    Hope u understand...Let me know.
    Arpanakumar and jamesexcel1970 like this.
  11. jamesexcel1970

    jamesexcel1970 Member

    Messages:
    85
    oh okay..getting.
  12. jamesexcel1970

    jamesexcel1970 Member

    Messages:
    85
    Champ.

    Can we change the path what am looking for?
  13. Monty

    Monty Well-Known Member

    Messages:
    631
    Yes ofcourse

    Mention your path by replacing actveworkbook.path.
    Arpanakumar and jamesexcel1970 like this.
  14. jamesexcel1970

    jamesexcel1970 Member

    Messages:
    85
    Yes got it

    Code (vb):
    wb.SaveAs ThisWorkbook.Path & "\" & ws.Name

    wb.SaveAs "C:\Users\JamesNotin\Links\Test" & "\" & ws.Name
     
    Monty likes this.
  15. Monty

    Monty Well-Known Member

    Messages:
    631
    Yes u are right James.
    Arpanakumar and jamesexcel1970 like this.
  16. jamesexcel1970

    jamesexcel1970 Member

    Messages:
    85
    Thank you.

    But need to see the path at office, am sure path would be same and folder may change as per the week..Have to think over here, do not want somebody in the team to go and change path every time.
  17. Monty

    Monty Well-Known Member

    Messages:
    631
    Can be done other way round.

    Go to your main sheet in cell A1 cell paste the path.
    Arpanakumar and jamesexcel1970 like this.
  18. jamesexcel1970

    jamesexcel1970 Member

    Messages:
    85
    Champ think am not getting it.
  19. Monty

    Monty Well-Known Member

    Messages:
    631
    Code (vb):
    Sub Saveas_Workbook()

    'Step 1:  Declare all the variables.
     Dim ws As Worksheet
      Dim wb As Workbook
      Dim Mypath as string
      Mypath =Sheet1.range("A1").value
    'Step 2:  Start the looping through sheets
     For Each ws In ThisWorkbook.Worksheets

    'Step 3:  Create new workbook and save it.
     Set wb = Workbooks.Add
        'wb.SaveAs ThisWorkbook.Path & "\" & ws.Name
       wb.SaveAs Mypath & "\" & ws.Name

    'Step 4:  Copy the target sheet to the new workbook
     ws.Copy Before:=wb.Worksheets(1)
        wb.Close SaveChanges:=True

    'Step 5:  Loop back around to the next worksheet
     Next ws
    End sub
    Take some time to compare and see difference in the code.

    And endure you copy and paste path weekly in A1 cell.
  20. jamesexcel1970

    jamesexcel1970 Member

    Messages:
    85
    Yes.Noted...YOuuuuuuuuuuuu Made my weekend.
    Hopefully it should work on monday on actuals files.
    It saves lot of time for the team.
    God bless you.
    Cheers.
  21. jamesexcel1970

    jamesexcel1970 Member

    Messages:
    85
    How to limit range on excel sheet of using by user.

    Example, i wanted the user can edit only from A1: C20 only, rest should not be click...can we restrict.
  22. Monty

    Monty Well-Known Member

    Messages:
    631
    Yes we can by going to sheet properties ( Scroll area ).
    Arpanakumar and jamesexcel1970 like this.
  23. Monty

    Monty Well-Known Member

    Messages:
    631
    Code (vb):
    Sheets("Sheet1").ScrollArea="A1:C20"


    With VBA...May be you can use workbook open event to write below line of code.
    Arpanakumar and jamesexcel1970 like this.
  24. jamesexcel1970

    jamesexcel1970 Member

    Messages:
    85
    Yes! This is expected.
    Tx.
  25. Monty

    Monty Well-Known Member

    Messages:
    631
    You are most welcome James.
    jamesexcel1970 and Arpanakumar like this.

Share This Page