[Solved] Property Management Template Data Add

[jekelkat]

I downloaded a Property Management Template that has a Master Data Sheet and multiple "Property" tabs. The template does not have enough tabs (nor columns in the Master) for all of our locations. I attempted to copy, add and VBA additional tabs that would feed into the Master Template & vice versa but nothing is working for me. There are only 12 Property columns/tabs and I need at least 100. I'm sorry if this is an elementary question, but I have tried to get this done watching demos with no luck. Please help! TYSM! I have attached the file so you can see what I'm working with.

 

[shamimarita]

I downloaded a Property Management Template that has a Master Data Sheet and multiple "Property" tabs. The template does not have enough tabs (nor columns in the Master) for all of our locations. I attempted to copy, add and VBA additional tabs that would feed into the Master Template & vice versa but nothing is working for me. There are only 12 Property columns/tabs and I need at least 100. I'm sorry if this is an elementary question, but I have tried to get this done watching demos with no luck. Please help! TYSM! I have attached the file so you can see what I'm working with.

Hello Jekelkat,

Used VBA to create 100 property sheets and to update the Master Data sheet.

First, create the property sheets using the following code:

Sub CreatePropertySheets()
    Dim i As Integer
    Dim ws As Worksheet
    Dim newWs As Worksheet
    Dim lastPropertySheet As Worksheet
    Dim masterWs As Worksheet
    Dim newColumn As Integer
    
    ' Find the last Property sheet
    Set lastPropertySheet = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count - 1)
    
    ' Create additional Property sheets up to 100
    For i = 13 To 100
        lastPropertySheet.Copy After:=lastPropertySheet
        Set newWs = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        newWs.Name = "Property " & i
        Set lastPropertySheet = newWs
    Next i
    
    ' Update the Master Data Sheet
    Set masterWs = ThisWorkbook.Sheets("Property Management Overview")
    
    ' Find the last column in the Master Data Sheet
    newColumn = masterWs.Cells(1, masterWs.Columns.Count).End(xlToLeft).Column + 1
    
    ' Add new columns for each Property
    For i = 13 To 100
        masterWs.Cells(1, newColumn).Value = "Property " & i
        newColumn = newColumn + 1
    Next i
End Sub

Next, use the following code to update the Master Data based on the property sheet.

Sub AddPropertyColumns()

Dim masterWs As Worksheet
Dim nextCol As Integer
Dim ytdCol As Integer
Dim i As Integer
Dim cell As Range

' Set the Master Data Sheet
Set masterWs = ThisWorkbook.Sheets("Property Management Overview")

' Find the YTD column index
Set cell = masterWs.Rows(2).Find("YTD TOTAL", LookIn:=xlValues, LookAt:=xlWhole)
If Not cell Is Nothing Then
ytdCol = cell.Column
nextCol = ytdCol ' Start inserting before YTD column
Else
MsgBox "YTD TOTAL column not found!", vbCritical
Exit Sub
End If

' Add columns for Property 13 to 100 following the format and formulas of Column N
For i = 100 To 13 Step -1
masterWs.Columns(nextCol).Insert Shift:=xlToRight
masterWs.Columns("N").Copy
masterWs.Columns(nextCol).PasteSpecial Paste:=xlPasteFormats
masterWs.Columns(nextCol).PasteSpecial Paste:=xlPasteFormulas
masterWs.Cells(2, nextCol).Value = i
masterWs.Cells(2, nextCol).Value = i
Next i

Call ProcessData

End Sub

Sub ProcessData()

Dim ws As Worksheet
Dim lastCol As Integer
Dim countValue As Integer
Dim currentCol As Integer
Dim formulaRow As Integer

Set ws = ThisWorkbook.Sheets("Property Management Overview")

lastR = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row

For currentRow = 10 To lastR
If ws.Cells(currentRow, 2).Value <> "TOTAL EXPENSES" Then
countV = countV + 1
Else
Exit For
End If
Next currentRow

lastCol = ws.Cells(8, ws.Columns.Count).End(xlToLeft).Column

countValue = 0
For currentCol = 3 To lastCol
If ws.Cells(8, currentCol).Value <> "YTD TOTAL" Then
countValue = countValue + 1
ws.Cells(8, currentCol).Value = countValue
Else
Exit For

End If
    Next currentCol
    formulaRow = 10
    For currentCol = 3 To countValue + 2
        ws.Cells(formulaRow, currentCol).Formula = "='Property " & (currentCol - 2) & "'!O11"
        ws.Cells(4, currentCol).Formula = "='Property " & (currentCol - 2) & "'!O5"
        ws.Cells(5, currentCol).Formula = "='Property " & (currentCol - 2) & "'!O6"
        ws.Range(ws.Cells(10, currentCol), ws.Cells(countV + 9, currentCol)).FillDown
    Next currentCol
End Sub

Here is the final Workbook;