Results 1 to 13 of 13

Split worksheet content into multiple worksheets

Threaded View

  1. #1
    Registered User
    Join Date
    01-12-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    3

    Split worksheet content into multiple worksheets

    Hello

    Thank you for taking the time to help me with this problem.

    My excel requirements have been VERY basic until now. (never created a macro / sum etc.)

    I've spent many hours 'google-ing' my problem without success. (I know my solution is out there )

    Using a macro, I need to split a worksheet based on the content of column A.

    i.e.

    Site 003
    Site 004

    Creating a new worksheet of the same name. Please see attached document.

    I have found some VB code to use.... but am stuck (sorry my formatting probably stinks!)
    Sub SeperateReports()
        Dim ThisReport As Range
        Dim NextReport As Range
        Dim NewSheetName As String
        Dim SiteNum As Integer
        Dim MaxSiteNum As Integer
        Dim SiteName As String
        Dim Rows As Integer
             
         ActiveWorkbook.SaveAs Filename:="D:\NewFile.xls"
       
        SiteNum = 1
        MaxSiteNum = 44
        
        Set ThisReport = Worksheets("Data").Range("A1")
              
        Do Until SiteNum = MaxSiteNum + 1
        
            SiteName = "Site" & SiteNum
            
            Sheets("Data").Select
            Rows = Application.WorksheetFunction.CountIf(Columns("A:A"), SiteName)
           
            If Rows > 0 Then
                      
            NewSheetName = SiteName
            Sheets.Add
            ActiveSheet.Name = NewSheetName
            Worksheets("Data").Activate
            ThisReport.Select
             
            
    '  Stuck here ... need to select for copy all rows with Site 003 ... then Site 004 etc. 
    '  Majority of below from copied code so may not be relevant
            If SiteNum < MaxSiteNum Then Set NextReport = Cells.Find(What:="?", _
            After:=ThisReport) Else Set NextReport = Range("A1").End(xlDown).Offset(1, 0)
            Range(ThisReport, NextReport.Offset(-1, 0)).EntireRow.Select
    
            
            Selection.Copy
            Sheets(NewSheetName).Select
            Range("A1").Select
            ActiveSheet.Paste
            Sheets("Data").Select
            Application.CutCopyMode = False
            SiteNum = SiteNum + 1
            
            Set ThisReport = NextReport
            ThisReport.Select
            
            Else
         Loop
              
    End Sub
    Please advise if possible... and possibly point me in the right direction of some documentation that may help.

    Let me know if you need more info... or if I'm not making sense.

    Thank you!
    Chris
    Attached Files Attached Files
    Last edited by Paul; 01-12-2010 at 10:20 PM. Reason: Added code tags for new user. Please read the forum rules.

Thread Information

Users Browsing this Thread

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

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