Results 1 to 2 of 2

User Input to Split the Table

Threaded View

  1. #1
    Registered User
    Join Date
    10-19-2010
    Location
    India
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    1

    User Input to Split the Table

    Hi Friends,

    I got the following Coding from one of the forum. The coding is used to Split the Spreadsheet into Multiple Tables.

    How to get the number of columns as user Input, and to split the spread sheet into multiple tables as per user input requirements.
    Sub moveIt()
        Dim rColA As Range, sMstr As Worksheet, sNew As Worksheet
        Dim i As Integer, j As Integer
        Dim lCol As Integer
        Application.ScreenUpdating = False
        Set sMstr = Sheets(1)
        Set rColA = sMstr.Range("A1:A10")
        lCol = sMstr.Cells(1, Columns.Count).End(xlToLeft).Column
    
        For i = 2 To lCol Step 5
            j = j + 1
            Set sNew = Sheets.Add(after:=Sheets(Sheets.Count))
            sNew.Name = "Output " & Format(j, "00")
            rColA.Copy
            sNew.Cells(1, 1).PasteSpecial xlPasteAll
            Range(sMstr.Cells(1, i), sMstr.Cells(10, i + 4)).Copy Destination:=sNew.Cells(1, 2)
            sNew.Cells.Columns.AutoFit
            sNew.Range("a1").Select
        Next
        sMstr.Activate
        sMstr.Range("a1").Select
        Application.ScreenUpdating = True
    End Sub
    Thanks,
    Rakesh
    Last edited by Leith Ross; 10-19-2010 at 08:09 PM. Reason: Added Code Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1