Hi all
I suspect I've missed something basic in this but I'm trying to get the macro to work across a variable number of columns which are defined in A1. The loop will always start in column 28 and then go out the specified number of times
Sub RefreshPurchasing()
Dim a, b, MAXVALUE, lcol As Long
Sheets("Purchasing Output").Select
lcol = 28
MAXVALUE = Sheets("Purchasing Output").Range("A1") * 2
For b = 1 To MAXVALUE
Range(Columns(lcol) & 2).Select
Range((ActiveCell.Column) & 2) = "=IFERROR(INDEX(SheetNames," & (ActiveCell.Column) & "2), """")"
lcol = lcol + 1
b = b + 1
Next b
b = MAXVALUE
Application.ScreenUpdating = True
End Sub
If there is a better way to achieve refreshing the sheet names in the cells or improving the above I'll take it all on board.
Thanks in advance
I suspect I've missed something basic in this but I'm trying to get the macro to work across a variable number of columns which are defined in A1. The loop will always start in column 28 and then go out the specified number of times
Sub RefreshPurchasing()
Dim a, b, MAXVALUE, lcol As Long
Sheets("Purchasing Output").Select
lcol = 28
MAXVALUE = Sheets("Purchasing Output").Range("A1") * 2
For b = 1 To MAXVALUE
Range(Columns(lcol) & 2).Select
Range((ActiveCell.Column) & 2) = "=IFERROR(INDEX(SheetNames," & (ActiveCell.Column) & "2), """")"
lcol = lcol + 1
b = b + 1
Next b
b = MAXVALUE
Application.ScreenUpdating = True
End Sub
If there is a better way to achieve refreshing the sheet names in the cells or improving the above I'll take it all on board.
Thanks in advance