VBS code cycle thru sheets doesn't work

Glenn Nelson 0 Reputation points
2023-03-03T15:29:01.26+00:00

I am trying to cycle thru sheets to perform code. It works on the first sheet, but not the others. The code fails (stops) at the Set rngTarget expression.

For Each sheet In ActiveWorkbook.Worksheets
    If sheet.Index > 1 Then
        Set rngTarget = sheet.Range(Cells(1, 8), Cells(1, arrSize))
Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,524 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,571 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Viorel 112.9K Reputation points
    2023-03-03T15:54:14.2266667+00:00

    Try this:

    Set rngTarget = sheet.Range(sheet.Cells(1, 8), sheet.Cells(1, arrSize))
    
    1 person found this answer helpful.
    0 comments No comments