macro running on timer fails to select proper sheet
 

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

macro running on timer fails to select proper sheet
 

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

macro running on timer fails to select proper sheet
 

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.