0

I have one workbook with a giant worksheet, say 4000 lines. I want to copy those lines and paste it into another workbook so that it will be split up and have 4 separate sheets with 1000 lines each. I am using visual basic and this is what I have so far.

ReadOnly FileName As String = "C:\Programming\LoadingFiles\MyTest1.xlsx"
ReadOnly DestinationFileName As String = "C:\Programming\LoadingFiles\Cache\Result.xlsx"
Sub SplitIntoMultipleSheets()
    Dim xlApp As New Application
    Dim xlDestinationApp As New Application
    Dim xlSourceWkbk As Workbook = xlApp.Workbooks.Open(FileName)
    Dim xlDestinationWkbk As Workbook = xlDestinationApp.Workbooks.Open(DestinationFileName)

    Try
        Console.Write("Source sheet count: " + xlSourceWkbk.Sheets.Count.ToString() + " ")
        Console.Write("~ Destination sheet count: " + xlDestinationWkbk.Sheets.Count.ToString())

        xlDestinationWkbk.Worksheets.Add(After:=xlDestinationWkbk.Sheets(xlDestinationWkbk.Sheets.Count)
                                         )

        xlDestinationWkbk.Save()

        xlDestinationWkbk.Close()
        xlSourceWkbk.Close()
        System.Diagnostics.Process.Start(DestinationFileName)
    Catch ex As Exception
        xlDestinationWkbk.Close()
        xlSourceWkbk.Close()
    End Try

End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
Robin Nguyen
  • 1
  • 1
  • I think what you should do is create 4 new worksheets on the destination workbook, then proceed to use the Range property to copy whichever lines from the source to the destination "new sheets." Something like what is referenced here: https://www.thesmallman.com/copying-data-with-vba; – aguertin Nov 09 '22 at 23:37
  • There are two ways here, one is to save the code in Excel and just call it from VBScript. Another is to put everything into VBS. https://stackoverflow.com/questions/50904762/vb-script-to-split-data-into-multiple-worksheets-based-on-column-in-excel – Hao Yu-MSFT Nov 23 '22 at 07:16

0 Answers0