How do I make this macro work for multiple sheets?

[bask1022]

My worksheet has a big table that contains job info, with weeks listed across the top and tasks down the side. I have this great macro (below) that draws data from the table and puts it into a tab called WEEK1. It works great. Problem is, I need to also pull data and put it into WEEK2, WEEK3, etc. Basically, I need to open one of the tabs and run the macro so it will work for the week I have open.

I highlighted in red the areas that have to change depending on the WEEK. Week1 pulls from column FO, Week2 from FP, and so on. And of course the display field locations change as the column increments by 1.

Can I achieve this somehow with a wildcard or by embedding the columns/fields I need in the open WEEK tab?

Sub Macro1()
'
' Macro1 Macro
'
Dim c As Range, rng
 Dim lr As Long
 Dim col_a_val As String
 Dim col_b_val As String
 Dim col_fh_val As String
 Dim col_[COLOR=#ff0000]fo[/COLOR]_val As String




 Sheets("ToDo").Activate


 lr = Range("fo" & Rows.Count).End(xlUp).Row
 Set rng = Range("[COLOR=#ff0000]fo5:fo[/COLOR]" & lr)
 For Each c In rng
 If c.Value > 0 Then
 col_c_val = c.Offset(0, [COLOR=#ff0000]-168[/COLOR]).Value
 col_d_val = c.Offset(0, [COLOR=#ff0000]-167[/COLOR]).Value
 col_fh_val = c.Offset(0, [COLOR=#ff0000]-7[/COLOR]).Value
 col_[COLOR=#ff0000]fo[/COLOR]_val = c.Offset(0, 0).Value
 
 Sheets[COLOR=#ff0000]("Week1"[/COLOR]).Activate
 lr = Range("a" & Rows.Count).End(xlUp).Row + 1
 Range("a" & lr) = col_c_val
 Range("b" & lr) = col_d_val
 Range("c" & lr) = col_[COLOR=#ff0000]fo[/COLOR]_val
 Range("d" & lr) = col_fh_val
 Sheets("ToDo").Activate
 End If
 Next c
'
End Sub

 

[Fluff]

Maybe, something like this

   Dim Clm As Long
   
   If IsNumeric(Right(ActiveSheet.Name, 2)) Then
      Clm = Right(ActiveSheet.Name, 2) + 170
   Else
      Clm = Right(ActiveSheet.Name, 1) + 170
   End If
   
   lr = Cells(Rows.Count, Clm).End(xlUp).Row
   Set Rng = Range(Cells(5, Clm), Cells(lr, Clm))