Re: Excel Spreadsheet split up to many worksheets

[Mike Wills <mike@xxxxxxxxxxxx>]

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.
>
>
>
>
> --
> This is the PC Technical Discussion for iSeries Users (PcTech) mailing list
> To post a message email: PcTech@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/pctech
> or email: PcTech-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/pctech.