VBA to export current different sheets to individual workbook










0















Sub SplitWorkbook()
'Updateby20140612
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim xWs As Worksheet
Dim xWb As Workbook
Dim FolderName As String
Application.ScreenUpdating = False
Set xWb = Application.ThisWorkbook
DateString = Format(Now, "yyyy-mm-dd hh-mm-ss")
FolderName = xWb.Path & "" & xWb.Name & " " & DateString
MkDir FolderName
For Each xWs In xWb.Worksheets
xWs.Copy
If Val(Application.Version) < 12 Then
FileExtStr = ".xls": FileFormatNum = -4143
Else
Select Case xWb.FileFormat
Case 51:
FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If Application.ActiveWorkbook.HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56:
FileExtStr = ".xls": FileFormatNum = 56
Case Else:
FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
xFile = FolderName & "" & Application.ActiveWorkbook.Sheets(1).Name & FileExtStr
Application.ActiveWorkbook.SaveAs xFile, FileFormat:=FileFormatNum
Application.ActiveWorkbook.Close False
Next
MsgBox "You can find the files in " & FolderName
Application.ScreenUpdating = True
End enter code here


when use I run this code, I encountered error on the line 14 "xWs.Copy" with an msg "run time error '1004' Method 'copy' of object'_worksheet failed.
Is there any way to short this lengthy formula?
All I just want is to export current workbook with different number of sheets to individual workbook with 1 sheet in it.










share|improve this question


























    0















    Sub SplitWorkbook()
    'Updateby20140612
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim xWs As Worksheet
    Dim xWb As Workbook
    Dim FolderName As String
    Application.ScreenUpdating = False
    Set xWb = Application.ThisWorkbook
    DateString = Format(Now, "yyyy-mm-dd hh-mm-ss")
    FolderName = xWb.Path & "" & xWb.Name & " " & DateString
    MkDir FolderName
    For Each xWs In xWb.Worksheets
    xWs.Copy
    If Val(Application.Version) < 12 Then
    FileExtStr = ".xls": FileFormatNum = -4143
    Else
    Select Case xWb.FileFormat
    Case 51:
    FileExtStr = ".xlsx": FileFormatNum = 51
    Case 52:
    If Application.ActiveWorkbook.HasVBProject Then
    FileExtStr = ".xlsm": FileFormatNum = 52
    Else
    FileExtStr = ".xlsx": FileFormatNum = 51
    End If
    Case 56:
    FileExtStr = ".xls": FileFormatNum = 56
    Case Else:
    FileExtStr = ".xlsb": FileFormatNum = 50
    End Select
    End If
    xFile = FolderName & "" & Application.ActiveWorkbook.Sheets(1).Name & FileExtStr
    Application.ActiveWorkbook.SaveAs xFile, FileFormat:=FileFormatNum
    Application.ActiveWorkbook.Close False
    Next
    MsgBox "You can find the files in " & FolderName
    Application.ScreenUpdating = True
    End enter code here


    when use I run this code, I encountered error on the line 14 "xWs.Copy" with an msg "run time error '1004' Method 'copy' of object'_worksheet failed.
    Is there any way to short this lengthy formula?
    All I just want is to export current workbook with different number of sheets to individual workbook with 1 sheet in it.










    share|improve this question
























      0












      0








      0








      Sub SplitWorkbook()
      'Updateby20140612
      Dim FileExtStr As String
      Dim FileFormatNum As Long
      Dim xWs As Worksheet
      Dim xWb As Workbook
      Dim FolderName As String
      Application.ScreenUpdating = False
      Set xWb = Application.ThisWorkbook
      DateString = Format(Now, "yyyy-mm-dd hh-mm-ss")
      FolderName = xWb.Path & "" & xWb.Name & " " & DateString
      MkDir FolderName
      For Each xWs In xWb.Worksheets
      xWs.Copy
      If Val(Application.Version) < 12 Then
      FileExtStr = ".xls": FileFormatNum = -4143
      Else
      Select Case xWb.FileFormat
      Case 51:
      FileExtStr = ".xlsx": FileFormatNum = 51
      Case 52:
      If Application.ActiveWorkbook.HasVBProject Then
      FileExtStr = ".xlsm": FileFormatNum = 52
      Else
      FileExtStr = ".xlsx": FileFormatNum = 51
      End If
      Case 56:
      FileExtStr = ".xls": FileFormatNum = 56
      Case Else:
      FileExtStr = ".xlsb": FileFormatNum = 50
      End Select
      End If
      xFile = FolderName & "" & Application.ActiveWorkbook.Sheets(1).Name & FileExtStr
      Application.ActiveWorkbook.SaveAs xFile, FileFormat:=FileFormatNum
      Application.ActiveWorkbook.Close False
      Next
      MsgBox "You can find the files in " & FolderName
      Application.ScreenUpdating = True
      End enter code here


      when use I run this code, I encountered error on the line 14 "xWs.Copy" with an msg "run time error '1004' Method 'copy' of object'_worksheet failed.
      Is there any way to short this lengthy formula?
      All I just want is to export current workbook with different number of sheets to individual workbook with 1 sheet in it.










      share|improve this question














      Sub SplitWorkbook()
      'Updateby20140612
      Dim FileExtStr As String
      Dim FileFormatNum As Long
      Dim xWs As Worksheet
      Dim xWb As Workbook
      Dim FolderName As String
      Application.ScreenUpdating = False
      Set xWb = Application.ThisWorkbook
      DateString = Format(Now, "yyyy-mm-dd hh-mm-ss")
      FolderName = xWb.Path & "" & xWb.Name & " " & DateString
      MkDir FolderName
      For Each xWs In xWb.Worksheets
      xWs.Copy
      If Val(Application.Version) < 12 Then
      FileExtStr = ".xls": FileFormatNum = -4143
      Else
      Select Case xWb.FileFormat
      Case 51:
      FileExtStr = ".xlsx": FileFormatNum = 51
      Case 52:
      If Application.ActiveWorkbook.HasVBProject Then
      FileExtStr = ".xlsm": FileFormatNum = 52
      Else
      FileExtStr = ".xlsx": FileFormatNum = 51
      End If
      Case 56:
      FileExtStr = ".xls": FileFormatNum = 56
      Case Else:
      FileExtStr = ".xlsb": FileFormatNum = 50
      End Select
      End If
      xFile = FolderName & "" & Application.ActiveWorkbook.Sheets(1).Name & FileExtStr
      Application.ActiveWorkbook.SaveAs xFile, FileFormat:=FileFormatNum
      Application.ActiveWorkbook.Close False
      Next
      MsgBox "You can find the files in " & FolderName
      Application.ScreenUpdating = True
      End enter code here


      when use I run this code, I encountered error on the line 14 "xWs.Copy" with an msg "run time error '1004' Method 'copy' of object'_worksheet failed.
      Is there any way to short this lengthy formula?
      All I just want is to export current workbook with different number of sheets to individual workbook with 1 sheet in it.







      vba export basic






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Nov 12 '18 at 8:55









      chee seng ngchee seng ng

      248




      248






















          0






          active

          oldest

          votes











          Your Answer






          StackExchange.ifUsing("editor", function ()
          StackExchange.using("externalEditor", function ()
          StackExchange.using("snippets", function ()
          StackExchange.snippets.init();
          );
          );
          , "code-snippets");

          StackExchange.ready(function()
          var channelOptions =
          tags: "".split(" "),
          id: "1"
          ;
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function()
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled)
          StackExchange.using("snippets", function()
          createEditor();
          );

          else
          createEditor();

          );

          function createEditor()
          StackExchange.prepareEditor(
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: true,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: 10,
          bindNavPrevention: true,
          postfix: "",
          imageUploader:
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          ,
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          );



          );













          draft saved

          draft discarded


















          StackExchange.ready(
          function ()
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53258668%2fvba-to-export-current-different-sheets-to-individual-workbook%23new-answer', 'question_page');

          );

          Post as a guest















          Required, but never shown

























          0






          active

          oldest

          votes








          0






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes















          draft saved

          draft discarded
















































          Thanks for contributing an answer to Stack Overflow!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid


          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.

          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function ()
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53258668%2fvba-to-export-current-different-sheets-to-individual-workbook%23new-answer', 'question_page');

          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          Use pre created SQLite database for Android project in kotlin

          Darth Vader #20

          Ondo