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
Bookmarks