none
Attempting to modify multiple worksheets from a single macro... is it possible? RRS feed

  • Question

  • I have an excel spreadsheet that contains multiple worksheets. I would like to be able to modify cells in four different worksheets but I want to do it all in one macro.

    When I start in Sheet1, I can activate Sheet2, but the cell references are still pointing to Sheet1.  How do I get around this?

    Below is a portion of the code I'm using. Basically, I'm checking a date reference to find the correct column to work in.  When the dates match, I use that column number to manipulate data.  However, even though I activate the "Handicap" sheet, the references to Range("x4").Offset(0, ColCount).Value and Range("bs4").Offset(0, 0).Value are still pointing to the "Standings" sheet.  How do correct this?

    Sub SetUpForNextWeek()
        Dim RowCount As Integer
        Dim ColCount As Integer
        Dim DateMatch As Boolean
        Dim SourceRange As Range
        Dim fillRange As Range
           
        RowCount = 0
        ColCount = 0
        DateMatch = False
                   
        Application.ScreenUpdating = False
        Worksheets("Handicap").Activate
        Do Until DateMatch = True
            If Range("x4").Offset(0, ColCount).Value <> Range("bs4").Offset(0, 0).Value Then
                ColCount = ColCount + 1
            Else
                DateMatch = True
            End If
           
        Loop

    Thanks in advance,

    Vinny


    Vinny

    Thursday, July 2, 2015 2:30 PM

Answers

  • Vinny,

    Look for the diffrence between your code:

        If Range("x4").Offset(0, ColCount).Value <> Range("bs4").Offset(0, 0).Value Then


    and the code Hans wrote:

     If .Range("x4").Offset(0, ColCount).Value <> .Range("bs4").Offset(0, 0).Value Then

    and then you can do the same for the other three worksheets.

    Jan


    • Edited by jgkzdl Thursday, July 2, 2015 5:29 PM
    • Marked as answer by Vpp1253 Thursday, July 2, 2015 5:42 PM
    • Unmarked as answer by Vpp1253 Thursday, July 2, 2015 5:42 PM
    • Marked as answer by Vpp1253 Thursday, July 2, 2015 5:43 PM
    Thursday, July 2, 2015 5:28 PM

All replies

  • If Handicap is the active sheet, Range("x4") should refer to cell X4 on the Handicap sheet. But there is a better way:

        ...
        Application.ScreenUpdating = False
        With Worksheets("Handicap")
            Do Until DateMatch = True
                If .Range("x4").Offset(0, ColCount).Value <> .Range("bs4").Offset(0, 0).Value Then
                    ColCount = ColCount + 1
                Else
                    DateMatch = True
                End If
            Loop
        End With

    Within the With Worksheets("Handicap") ... End With block, anything beginning with . refers to Worksheets("Handicap"), so .Range("x4") is equivalent to Worksheets("Handicap").Range("x4").


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, July 2, 2015 3:06 PM
  • Thank you Hans, however, I tried the With statements and unfortunately, there is no difference.  Both values of "x4" and "bs4" are empty when I mouse over them in the code block.    There are dates populated in both cells of the correct worksheet.

    Application.ScreenUpdating = False
        With Worksheets("Handicap")
            Do Until DateMatch = True
                If Range("x4").Offset(0, ColCount).Value <> Range("bs4").Offset(0, 0).Value Then
                ColCount = ColCount + 1
                Else
                    DateMatch = True
                End If
           
            Loop

         End With

    Other suggestions or does mine look incorrect in some way?


    Vinny

    Thursday, July 2, 2015 3:33 PM
  • So, you want to loop through all sheets, and NOT perform any work on the 'Handicap' sheet, right.  It will be something like this . . .

    Dim Current As Worksheet

    For Each Current In Worksheets
    On Error Resume Next
    Current.Select

    If Current.Name <> "Handicap" And Current.Name <> "Another Sheet" Then

    ' DO YOUR WORK HERE

    End If
    Next


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, July 2, 2015 4:00 PM
  • No, not really. There are only 4 sheets I need to work on out of potentially 25 to 30.  the Handicap sheet is the last one of the four that needs to be modified. I keep the stats for a golf league and each week new stats are added for that week.  After updating everything, I need to prepare the spreadsheet for next week's entries which entails moving and correcting values throughout several worksheets.  The problem is, it needs to be done in a certain order so as not to lose data that's coming from other worksheets that don't require changes. 

    I'm totally confused as to why things that are "supposed" to work, just don't.  As in the previous answer using the With statement.  Hans said that anything within the With and End With would be using the values in that specific worksheet, but that doesn't work either.


    Vinny

    Thursday, July 2, 2015 4:52 PM
  • Vinny,

    Look for the diffrence between your code:

        If Range("x4").Offset(0, ColCount).Value <> Range("bs4").Offset(0, 0).Value Then


    and the code Hans wrote:

     If .Range("x4").Offset(0, ColCount).Value <> .Range("bs4").Offset(0, 0).Value Then

    and then you can do the same for the other three worksheets.

    Jan


    • Edited by jgkzdl Thursday, July 2, 2015 5:29 PM
    • Marked as answer by Vpp1253 Thursday, July 2, 2015 5:42 PM
    • Unmarked as answer by Vpp1253 Thursday, July 2, 2015 5:42 PM
    • Marked as answer by Vpp1253 Thursday, July 2, 2015 5:43 PM
    Thursday, July 2, 2015 5:28 PM
  • Jan, you are a genius!  Thank you very much!

    Vinny

    Thursday, July 2, 2015 5:44 PM
  • Thank you very much Hans.  After Jan pointed out my mistake (missing the . before Range) everything worked perfectly! Your help is very much appreciated.

    Vinny


    Vinny

    Thursday, July 2, 2015 5:47 PM