Re: Excel Spreadsheet split up to many worksheets

[Jim Oberholtzer <midrangel@xxxxxxxxxx>]

Works for me but I grafted bits and pieces from various sources so my 
authorship is not  entirely original....

Jim Oberholtzer
Chief Technical Architect
Agile Technology Architects


On 8/15/2011 2:35 PM, Mike Wills wrote:
> If you don't mind, I'll post this on GitHub Gist for all to find and
> use. I'll link back to the origonal post for credit.
>
> --
> Mike Wills
> http://mikewills.me
>
>
>
> On Mon, Aug 15, 2011 at 2:19 PM, sjl<sjl_abc@xxxxxxxxxxx>  wrote:
> > >  Jim -
> > >  See the code I added (variable is wrkname)
> > >  - sjl
> > >
> > >
> > >  Jim wrote:
> > >  I think I have it figured out with this piece of VBA saved as a macro:
> > >
> > >  Sub split_up()
> > >
> > >  Dim rLastCell As Range
> > >
> > >  Dim rCells As Range
> > >  Dim strName As String
> > >  Dim lLoop As Long, lCopy As Long
> > >  Dim wbNew As Workbook
> > >  dim wrkname
> > >
> > >  With ThisWorkbook.Sheets(1)
> > >  Set rLastCell = .Cells.Find(What:="*", After:=[A1],
> > >  SearchDirection:=xlPrevious)
> > >
> > >  For lLoop = 1 To rLastCell.Row Step 2000
> > >  lCopy = lCopy + 1
> > >  Set wbNew = Workbooks.Add
> > >  .Range(.Cells(lLoop, 1), .Cells(lLoop + 2000,
> > >  .Columns.Count)).EntireRow.Copy _
> > >  Destination:=wbNew.Sheets(1).Range("A1")
> > >  wbNew.Close SaveChanges:=True, Filename:=wrkname&  lCopy&  "Rows"&
> > >  lLoop&  "-"&  lLoop + 2000
> > >  Next lLoop
> > >  End With
> > >
> > >  End Sub
> > >
> > >
> > >  What I don't know how to do is save the orininal workbook name instead
> > >  of "chunk"........  A modification to that line of code would be very cool.
> > >
> > >
> > >