Scornakira
New Member
- Joined
- Jul 8, 2022
- Messages
- 3
- Office Version
- 2016
- Platform
- Windows
I've been at this for a few days trying to get the correct code in place, and I know it's just there, but everything keeps coming up short.
I'm not quite sure how to implement the " & Rows.Count).End(xlUp).Offset(1) " correctly so that I start at the last cell in my columns, to implement code, and the few time's I've gotten it to work in that cell, it still tries to reference cell 1 of the column instead of current cell.
I don't really want it to add the code all the way down to cell 5000, I'd rather it just enter that data once at the desired cell. The current setup almost does what I want. It adds the formula from E1/F1 down to E5000/F5000, and referencing A1 to A5000 in the formula. I just want to input into the next available cell, and the formula reference the aligned A cell.
I'm not quite sure how to implement the " & Rows.Count).End(xlUp).Offset(1) " correctly so that I start at the last cell in my columns, to implement code, and the few time's I've gotten it to work in that cell, it still tries to reference cell 1 of the column instead of current cell.
I don't really want it to add the code all the way down to cell 5000, I'd rather it just enter that data once at the desired cell. The current setup almost does what I want. It adds the formula from E1/F1 down to E5000/F5000, and referencing A1 to A5000 in the formula. I just want to input into the next available cell, and the formula reference the aligned A cell.
VBA Code:
Sub updatedata()
'Start of Macro
Application.ScreenUpdating = False
Sheets("Site Roster").Visible = True
'Fill in Shift and Dept of Transcript
Sheets("Transcript").Select
With ActiveSheet
Range("E1:F1").Select
Range("$E1:$E5000").Formula = "=IFERROR(LEFT(INDEX('Site Roster'!$K:$K,MATCH($A1,'Site Roster'!$B:$B,0)),2),""-"")"
Range("$F1:$F5000").Formula = "=IFERROR(INDEX(Tracker!$B:$B,MATCH($A1,Tracker!$A:$A,0)),""-"")"
End With
'Refresh all pivot tables
Sheets("Landing").Select
With ActiveSheet
Dim pc As PivotCache
For Each pc In ThisWorkbook.PivotCaches
pc.Refresh
Next pc
'End of Macro
Application.ScreenUpdating = True
Sheets("Site Roster").Visible = False
Sheets("Landing").Select
End With
End Sub