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:
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
Code:
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;