• 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...

  • 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.

J

jamesexcel1970

Member
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.
 
Code: 
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.
 
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.
 
Code: 
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.
 
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.
 
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.
 
You must log in or register to reply here.
Back
Top