I have a macro that is set to run on a timer every 60 seconds. It repopulates
formulas in columns AA:AC of a sheet named "sales". This works fine, unless
I have another workbook open. The code is below. The question is , how can i
make it so the macro runs in the workbook it is contained in rather than
"activeworkbook?". The name of the sheet is not predictable so I cannot rely
on that. The workbook will always have "sales" as a sheet name though.

Private Sub task_sbformulas()
'populates indirect lookup formulas in sales sheet AC, AD, AE
ActiveWorkbook.Sheets("sales").Select
Range("AA2:IV65536").ClearContents
Range("AA1").Value = "sb.pages"
Range("AB1").Value = "sb.value"
Range("AC1").Value = "sb.cover"
Range("AA2:AA201").FormulaR1C1 =
"=IF(ISBLANK(INDIRECT(""c""&ROW())),"""",VLOOKUP(I NDIRECT(""c""&ROW()),tbl.specs,2,0))"
Range("AB2:AB201").FormulaR1C1 =
"=IF(ISBLANK(INDIRECT(""c""&ROW())),"""",VLOOKUP(I NDIRECT(""c""&ROW()),tbl.specs,5,0))"
Range("AC2:AC201").FormulaR1C1 =
"=IF(ISBLANK(INDIRECT(""sales!c""&ROW())),"""",VLO OKUP(INDIRECT(""sales!c""&ROW()),tbl.specs,6,0))"
End Sub

Change activeworkbook to thisworkbook... Additionally you need to get rid of
all of your selects as you con only select on an active sheet.

Private Sub task_sbformulas()
'populates indirect lookup formulas in sales sheet AC, AD, AE
with ThisWorkbook.Sheets("sales")
.Range("AA2:IV65536").ClearContents
.Range("AA1").Value = "sb.pages"
.Range("AB1").Value = "sb.value"
.Range("AC1").Value = "sb.cover"
.Range("AA2:AA201").FormulaR1C1 =
"=IF(ISBLANK(INDIRECT(""c""&ROW())),"""",VLOOKUP(I NDIRECT(""c""&ROW()),tbl.specs,2,0))"
.Range("AB2:AB201").FormulaR1C1 =
"=IF(ISBLANK(INDIRECT(""c""&ROW())),"""",VLOOKUP(I NDIRECT(""c""&ROW()),tbl.specs,5,0))"
.Range("AC2:AC201").FormulaR1C1 =
"=IF(ISBLANK(INDIRECT(""sales!c""&ROW())),"""",VLO OKUP(INDIRECT(""sales!c""&ROW()),tbl.specs,6,0))"
End Sub



--
HTH...

Jim Thomlinson


"Solutions Manager" wrote:

I have a macro that is set to run on a timer every 60 seconds. It repopulates
formulas in columns AA:AC of a sheet named "sales". This works fine, unless
I have another workbook open. The code is below. The question is , how can i
make it so the macro runs in the workbook it is contained in rather than
"activeworkbook?". The name of the sheet is not predictable so I cannot rely
on that. The workbook will always have "sales" as a sheet name though.

Private Sub task_sbformulas()
'populates indirect lookup formulas in sales sheet AC, AD, AE
ActiveWorkbook.Sheets("sales").Select
Range("AA2:IV65536").ClearContents
Range("AA1").Value = "sb.pages"
Range("AB1").Value = "sb.value"
Range("AC1").Value = "sb.cover"
Range("AA2:AA201").FormulaR1C1 =
"=IF(ISBLANK(INDIRECT(""c""&ROW())),"""",VLOOKUP(I NDIRECT(""c""&ROW()),tbl.specs,2,0))"
Range("AB2:AB201").FormulaR1C1 =
"=IF(ISBLANK(INDIRECT(""c""&ROW())),"""",VLOOKUP(I NDIRECT(""c""&ROW()),tbl.specs,5,0))"
Range("AC2:AC201").FormulaR1C1 =
"=IF(ISBLANK(INDIRECT(""sales!c""&ROW())),"""",VLO OKUP(INDIRECT(""sales!c""&ROW()),tbl.specs,6,0))"
End Sub

Thank you. I added a thisworkbook test to the macro, but didn't realize the
selects tip. Thank you for the help.

"Jim Thomlinson" wrote:

Change activeworkbook to thisworkbook... Additionally you need to get rid of
all of your selects as you con only select on an active sheet.