VBA Split into Separate Workbooks on number of rows

[mlindquist]

I have a process that I'm populating an excel spreadsheet and then I'm running it through a process from that excel file. I would like to just download the whole population but I can only process about 5,000 at a time. Is there a way to split into separate workbooks based on number of rows. Plus I want the workbook to keep the tabs that are currently in the spreadsheet when copying. Here is what I'm currently running to split within a spreadsheet but then I copy and paste into this other workbook that has all the tabs I need to run a process. Example the tab I want to split by is Data Input and then there are other tabs that I want to keep in the split workbook. Here is a split that I'm currently doing :

Sub SplitWorksheet()
    Dim lngLastRow As Long
    Dim lngNumberOfRows As Long
    Dim lngI As Long
    Dim strMainSheetName As String
    Dim currSheet As Worksheet
    Dim prevSheet As Worksheet
    'Number of rows to split among worksheets
    lngNumberOfRows = 3000
    'Current worksheet in workbook
    Set prevSheet = ThisWorkbook.ActiveSheet
    'First worksheet name
    strMainSheetName = prevSheet.Name
    'Number of rows in worksheet
    lngLastRow = prevSheet.Cells(Rows.Count, 1).End(xlUp).Row
    'Worksheet counter for added worksheets
    lngI = 1
    While lngLastRow > lngNumberOfRows
        Set currSheet = ThisWorkbook.Worksheets.Add
        With currSheet
           .Move after:=Worksheets(Worksheets.Count)
           .Name = strMainSheetName + "(" + CStr(lngI) + ")"
        End With

        With prevSheet.Rows(lngNumberOfRows + 1 & ":" & lngLastRow).EntireRow
            .Cut currSheet.Range("A1")
        End With

        lngLastRow = currSheet.Cells(Rows.Count, 1).End(xlUp).Row
        Set prevSheet = currSheet
        lngI = lngI + 1
    Wend
End Sub

What I would like to do is do a split by files (every 3,000 rows) on this one macro enabled spreadsheet that has 5 other tabs on it. That I also want to be included in the split. Right now what I'm doing is I have a workbook then I populate this one tab with the 50,000 rows and then I split into separate worksheets every 3,000 rows. Then I go to each tab (3000 rows) and copy and paste into this other workbook which has other macros that I run after I populate with the data. I might have to do this 50 some times. And then I have to keep track of what I have done by saving a new file.

 

[mlindquist]

I was hoping to edit my original post. What I meant to say is I have an excel workbook where I populate one of the sheets with data from another system and then I run a bunch of macros against this data and then mass load into a system. The problem is that the system cannot load more than 3,000 rows at a time without going haywire. So what I was hoping to be able to do is to populate this one tab with all the data (over 50,000 rows per business unit) and then run a macro that splits the data on this one tab by 3,000 rows and creates a new workbook but also includes 4 or 5 other tabs that are included on each split out file. So basically this is how it is:

I have one spreadsheet - it has the following tabs: Connect Information, Template, Data Input, Staging & Submission, Errors. The data will be populated on Data Input and when I split the workbook into separate files I want the Data Input to only have 3000 rows and then nothing else happens with the other tabs except that they are also copied to the new workbooks and then have the file name just add a number at the end and increment by one with each split.