I'm trying to create a table to show costs by floor. I've got toggles to turn them off or on, on a setup tab (I'll add pictures). The number of floors will vary, so I need that to update. I've got another tab that has the actual calculation and a third where the table is being built. Currently it works with the very first (last) floor but doesn't continue up the list. Also, I've defined the worksheets in my code but when I try to reference them, it wasn't working (Ex. Ws1.Range("P2:T2").Select wouldn't work, so I had to use Sheets("PM COST CODES").Select Range("P2:T2").Select) If I'm doing something wrong, I'd like to understand so I can fix it.
My current coding is below:
Sub CCBFB()
Dim nr As Long
Dim ws As Worksheet, ws1 As Worksheet, ws2 As Worksheet
Dim lastrow As Long
Dim lastcolumn As Long
Dim totalrow As Long
Dim lc As Range
Dim i As Long
Set ws = Worksheets("Setup")
Set ws1 = Worksheets("PM COST CODES")
Set ws2 = Worksheets("Cost Codes by Floor Build")
Application.ScreenUpdating = False
' Initialize to start at bottom of column K
nr = Rows.Count
' last row in column K
lastrow = ws.Cells(Rows.Count, 11).End(xlUp).Row
' Loop through rows
Do
' Find next row up with data
nr = Cells(nr, "K").End(xlUp).Row
' Exit if at row 2
If nr = 2 Then Exit Do
' Turn Off the current level
With Cells(nr, "N").Select
ActiveCell.FormulaR1C1 = "Off"
Cells(nr, "K").Select
Selection.Copy
Sheets("PM COST CODES").Select
Range("P2:T2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ws1.Columns("P:T").Select
Selection.Copy
' last column in Cost Codes by Floor Build
lastcolumn = ws2.Cells(5, Columns.Count).End(xlToLeft).Column
Sheets("Cost Codes by Floor Build").Select
Columns(lastcolumn + 1).Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
Ws.Cells(nr, "N").Select
ActiveCell.FormulaR1C1 = "Off"
Loop
My current coding is below:
Sub CCBFB()
Dim nr As Long
Dim ws As Worksheet, ws1 As Worksheet, ws2 As Worksheet
Dim lastrow As Long
Dim lastcolumn As Long
Dim totalrow As Long
Dim lc As Range
Dim i As Long
Set ws = Worksheets("Setup")
Set ws1 = Worksheets("PM COST CODES")
Set ws2 = Worksheets("Cost Codes by Floor Build")
Application.ScreenUpdating = False
' Initialize to start at bottom of column K
nr = Rows.Count
' last row in column K
lastrow = ws.Cells(Rows.Count, 11).End(xlUp).Row
' Loop through rows
Do
' Find next row up with data
nr = Cells(nr, "K").End(xlUp).Row
' Exit if at row 2
If nr = 2 Then Exit Do
' Turn Off the current level
With Cells(nr, "N").Select
ActiveCell.FormulaR1C1 = "Off"
Cells(nr, "K").Select
Selection.Copy
Sheets("PM COST CODES").Select
Range("P2:T2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ws1.Columns("P:T").Select
Selection.Copy
' last column in Cost Codes by Floor Build
lastcolumn = ws2.Cells(5, Columns.Count).End(xlToLeft).Column
Sheets("Cost Codes by Floor Build").Select
Columns(lastcolumn + 1).Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
Ws.Cells(nr, "N").Select
ActiveCell.FormulaR1C1 = "Off"
Loop
