0

I have a spreadsheet that is linked to multiple sheets other workbooks (different staff timesheets) on SharePoint. I have created the below sub to gather the information from cell C34 (credit/deficit hours accrued) on each sheet in the workbook for each staff member (to track their fortnightly workload) by creating a formula in the active cell that is a link to cell C34 for each staff member for the date from the Column A. what I need is for the macro to select the next active cell down in the active column and run the sub again so I don't need to Manually

Sub MakeFormlaeGreatAgain()
'Run Macro with Crtl + Shift + E

Dim formula As String
Dim folder As String
Dim location As String
Dim name As String
Dim extension As String
Dim row As Integer
Dim sheet As String
Dim cell As String
Dim timesheet As String
Dim column As Integer
Dim cellsAddress As String

row = ActiveCell.row

column = ActiveCell.column

folder = Sheets("Sheet1").Range("A1").Value

location = ActiveSheet.Range("A1").Value

timesheet = Sheets("Sheet1").Range("C1").Value

cellsAddress = Cells(2, column).Address
name = Sheets("Academic Plan").Range(cellsAddress).Value

extension = Sheets("Sheet1").Range("E1").Value

sheet = ActiveSheet.Range("A" & row).Value

cell = Sheets("Sheet1").Range("$G$1").Value

formula = "'" & folder & location & timesheet & name & extension & sheet & "'" & cell
Application.ActiveCell.Value = "=" & formula

End Sub
braX
  • 10,905
  • 5
  • 18
  • 32
vbaproblemchild
  • 1
  • `ActiveCell`, `ActiveSheet` and `Select` are the VBAProblemChild. Have a read of [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Might not solve the problem straight away, but will definitely help. – Darren Bartrup-Cook Nov 10 '21 at 08:29

0 Answers0