edmundmckay
New Member
- Joined
- Aug 24, 2015
- Messages
- 31
Hello
I am looking for some guidance on the below Macro. I have a table (Table1) on a tab named "Arrears Report". The table has a list of property names on it and their arrears. The table has 15 columns.
I am looking to generate a tab Per Property Name with the corresponding data for the property extracted from a table with header as per the table below. There are around 70 properties.
In the below macro I have typed number 2 as column reference. in the macro this is labelled as vcol. The macro works by generating the 70 tabs off the table. What it isn't doing is picking up all the properties in the list in column 2. Where there are properties which begin with the same name but have a different property code after, it generates a number blank sheet. In the below data with Burgess Hill Property Name it generates Burgess Hill (200400) as its own tab but creates Sheet3 with no data for Burgess Hill High Street (200201).
Could anyone help with updating the macro in order that the above problem doesn't happen?
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Property[/TD]
[TD]Property Name[/TD]
[TD]Charge To Invoice[/TD]
[TD]Invoice No[/TD]
[TD]Charge Code<strike></strike>[/TD]
[TD]Invoice<strike></strike>[/TD]
[TD]From Date<strike></strike>[/TD]
[TD]To Date<strike></strike>[/TD]
[TD]Total Charges<strike></strike>[/TD]
[TD]Total Owed<strike></strike>[/TD]
[TD]Accumulated<strike></strike>[/TD]
[TD]Comments<strike></strike>[/TD]
[TD]NRR Feed Back<strike></strike>[/TD]
[TD]PM Feedback<strike></strike>[/TD]
[/TR]
[TR]
[TD]200400[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR]
[TD]Burgess Hill (200400)[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]*****[/TD]
[TD]*****<strike></strike>[/TD]
[TD]*****[/TD]
[TD]*****<strike></strike>[/TD]
[TD]*****[/TD]
[TD]*****<strike></strike>[/TD]
[TD]*****[/TD]
[TD]*****<strike></strike>[/TD]
[TD]*****[/TD]
[TD]*****<strike></strike>[/TD]
[TD]*****[/TD]
[TD]*****<strike></strike>[/TD]
[/TR]
[TR]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD]200400[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR]
[TD]Burgess Hill (200400)[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD]200400[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR]
[TD]Burgess Hill (200400)[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[/TR]
[TR]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD]200201[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD][TABLE="width: 230"]
<tbody>[TR]
[TD]Burgess Hill High Street (200201)[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[/TR]
[TR]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD]200201[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD][TABLE="width: 230"]
<tbody>[TR]
[TD]Burgess Hill High Street (200201)[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[/TR]
[TR]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD]149400[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD][TABLE="width: 230"]
<tbody>[TR]
[TD]Erdington (149400)[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
<strike></strike>[/TD]
[/TR]
[TR]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD]149400[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD][TABLE="width: 230"]
<tbody>[TR]
[TD]Erdington (149400)[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[/TR]
</tbody>[/TABLE]
Macro code:
I look forward to any response
many thanks in advance
I am looking for some guidance on the below Macro. I have a table (Table1) on a tab named "Arrears Report". The table has a list of property names on it and their arrears. The table has 15 columns.
I am looking to generate a tab Per Property Name with the corresponding data for the property extracted from a table with header as per the table below. There are around 70 properties.
In the below macro I have typed number 2 as column reference. in the macro this is labelled as vcol. The macro works by generating the 70 tabs off the table. What it isn't doing is picking up all the properties in the list in column 2. Where there are properties which begin with the same name but have a different property code after, it generates a number blank sheet. In the below data with Burgess Hill Property Name it generates Burgess Hill (200400) as its own tab but creates Sheet3 with no data for Burgess Hill High Street (200201).
Could anyone help with updating the macro in order that the above problem doesn't happen?
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Property[/TD]
[TD]Property Name[/TD]
[TD]Charge To Invoice[/TD]
[TD]Invoice No[/TD]
[TD]Charge Code<strike></strike>[/TD]
[TD]Invoice<strike></strike>[/TD]
[TD]From Date<strike></strike>[/TD]
[TD]To Date<strike></strike>[/TD]
[TD]Total Charges<strike></strike>[/TD]
[TD]Total Owed<strike></strike>[/TD]
[TD]Accumulated<strike></strike>[/TD]
[TD]Comments<strike></strike>[/TD]
[TD]NRR Feed Back<strike></strike>[/TD]
[TD]PM Feedback<strike></strike>[/TD]
[/TR]
[TR]
[TD]200400[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR]
[TD]Burgess Hill (200400)[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]*****[/TD]
[TD]*****<strike></strike>[/TD]
[TD]*****[/TD]
[TD]*****<strike></strike>[/TD]
[TD]*****[/TD]
[TD]*****<strike></strike>[/TD]
[TD]*****[/TD]
[TD]*****<strike></strike>[/TD]
[TD]*****[/TD]
[TD]*****<strike></strike>[/TD]
[TD]*****[/TD]
[TD]*****<strike></strike>[/TD]
[/TR]
[TR]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD]200400[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR]
[TD]Burgess Hill (200400)[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD]200400[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR]
[TD]Burgess Hill (200400)[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[/TR]
[TR]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD]200201[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD][TABLE="width: 230"]
<tbody>[TR]
[TD]Burgess Hill High Street (200201)[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[/TR]
[TR]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD]200201[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD][TABLE="width: 230"]
<tbody>[TR]
[TD]Burgess Hill High Street (200201)[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[/TR]
[TR]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD]149400[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD][TABLE="width: 230"]
<tbody>[TR]
[TD]Erdington (149400)[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
<strike></strike>[/TD]
[/TR]
[TR]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD]149400[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD][TABLE="width: 230"]
<tbody>[TR]
[TD]Erdington (149400)[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[TD]*****
[/TD]
[TD]*****
<strike></strike>[/TD]
[/TR]
</tbody>[/TABLE]
Macro code:
Sub splitpropertyintotabsmacro()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Dim LR As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 2
Set ws = Sheets("Arrears Report")
LR = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = "A14:S14"
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
For i = 15 To LR
On Error Resume Next
If ws.Cells(i, vcol) <> "()" & "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
ws.Range("A" & titlerow & ":A" & LR).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
Sheets(myarr(i) & "").Columns.AutoFit
Next
ws.AutoFilterMode = False
ws.Activate
'Delete Total Tab'
'Stopping Application Alerts
Application.DisplayAlerts = False
Sheets("Total").Delete
'Enabling Application alerts once we are done with our task
Application.DisplayAlerts = True
Sheets("Arrears Report").Select
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Range("A1").Select
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
End Sub
I look forward to any response
many thanks in advance