loop works in module but not worksheet code

[Rodger]

Hey Gurus!

Any idea why this code works perfectly inside of a module, but not if i put it in a GoSub / Return routine in a sheet code?
It cycles thru the sheets, but does not perform the For/Next functions.
As I say tho, if I call the module with the exact same code, it works fine.

Months = Array("enter sales", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
            For Each Month In Months
            Worksheets(Month).Activate
            ActiveSheet.Unprotect
            For c = Range("D10").Column To Range("BL10").Column Step 2
              If Cells(9, c).Value = "Y" Then
              Range(Cells(x + 3, c), Cells(256, c)).Copy
              Cells(x + 4, c).PasteSpecial Paste:=xlPasteValues
              Cells(x + 3, c).Value = 0
              End If
            Next c

I've tried all I can think of, so time to ask for some thoughts.

Rodger

 

[GCExcel]

Hi,
It's because you use Activesheet to refer to the sheet you want to apply the For/Next. Since the code is inside a sheetcode, the activesheet is always the same (ie that sheet code).

When referring to a range or a cell, if is good practice to refer to the sheet name to prevent such problem.

Example
Sheet(Month).Range("D10")

When you have multiple "operations" on the same sheet, you can use the With/End With Statement.

Can you try this code and see if it works

  Months = Array("enter sales", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

  For Each Month In Months
          
      With Worksheets(Month)
        .Unprotect
        For c = .Range("D10").Column To .Range("BL10").Column Step 2
            If .Cells(9, c).Value = "Y" Then
              .Range(Cells(x + 3, c), .Cells(256, c)).Copy
              .Cells(x + 4, c).PasteSpecial Paste:=xlPasteValues
              .Cells(x + 3, c).Value = 0
            End If
        Next c
      End With
          
  Next Month

 

[Rodger]

Hi, thanks for taking a look!
I put your code in, and it gets hung on the first line after Then
.Range(Cells(x + 3, c), .Cells(256, c)).Copy
whether I use it in the sheet code, or separate module.

any thoughts?

Rodger

 

[GCExcel]

Can you share the rest of the code or even better your workbook?

 

[GCExcel]

My bad, I just saw the error, add a "." in front of Cells

.Range(.Cells(x + 3, c), .Cells(256, c)).Copy

 

[Rodger]

Fantastic !
works like a dream.
Thank you VERY much - this solves a lot of problems.

Cheers!

Rodger