Responsive Ad Area

Share This Post

test

Work Books Sheets copy to Multiple WorkBooks and Rename

I have workbook and there many sheets i want to copy one by one sheets to new work book and rename workbook

I tried, but it saved in one workbook instead of separate workbooks also i dont want to copy first worksheet to copy new workbook

Option Explicit

Sub CreateWorkBooks()

Dim ws As Object
Dim i As Long
Dim ws_num As Integer
Application.ScreenUpdating = False

Set ws = Worksheets



ws_num = ThisWorkbook.Worksheets.Count

For i = 2 To ws_num

      'Copy one worksheet as a new workbook
      'The new workbook becomes the ActiveWorkbook
      ws.Copy


      'Replace all formulas with values (optional)
      ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value


      'May want (not required) to add a file name extension (.xls or .xlsx) to the file name
      ActiveWorkbook.SaveAs ThisWorkbook.Path & "" & _
                           "AR Balance- " & ActiveSheet.Name & " " & Worksheets("DATA Sheet").Range("m2") & ".xlsx"


      ActiveWorkbook.Close SaveChanges:=False



Next 

Application.ScreenUpdating = True

End Sub


Work Books Sheets copy to Multiple WorkBooks and Rename
Work Books Sheets copy to Multiple WorkBooks and Rename
test
{$excerpt:n}

Share This Post

Leave a Reply

Your email address will not be Publishedd. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Skip to toolbar