Re: Excel Spreadsheet split up to many worksheets

[Jim Oberholtzer <midrangel@xxxxxxxxxx>]

OK, now I get it.....  Thanks.  I was wondering how wrkname got a value.

Thanks for the changes!!

Jim Oberholtzer
Chief Technical Architect
Agile Technology Architects


On 8/15/2011 2:21 PM, sjl wrote:
> Oops... didn't set the value of wrkname
> - sjl
>
>
>
> "Jim Oberholtzer"<midrangel@xxxxxxxxxx>  wrote in message
> news:mailman.2302.1313429397.2572.pctech@xxxxxxxxxxxx...
> 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
>
> wrkname = ThisWorkbook.name
>
> 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:="Chunk"&  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.
>
> Jim Oberholtzer
> Chief Technical Architect
> Agile Technology Architects
>
>
> On 8/15/2011 9:34 AM, Jim Oberholtzer wrote:
> > >  I have a very large workbook with one worksheet that has 53K plus rows
> > >  in it.  I need to split it up into 2000 row worksheets.  This is because
> > >  the software where the data will be uploaded to has a size limit on it's
> > >  up load.  (all local servers to me)
> > >
> > >  Does anyone have a technique or a macro that will split an excel
> > >  spreadsheet up into 2000 row sheets?
> > >
> > >  -- Jim Oberholtzer Chief Technical Architect Agile Technology Architects