Results 1 to 5 of 5

Not able to select replace all sheets

Threaded View

  1. #1
    Valued Forum Contributor tek9step's Avatar
    Join Date
    05-27-2009
    Location
    London, England
    MS-Off Ver
    MS 365
    Posts
    395

    Not able to select replace all sheets

    Hi

    The maco does select all sheets but only replacing the data on the sheet visible.. Can any one help with the VB thanks.

    Sub ALLTOTALTOMONTH()
    '
    ' Macro1 Macro
    'SELECT ALL SHEETS
     
     
    Dim wsSheet As Worksheet
    
    For Each wsSheet In ActiveWorkbook.Worksheets
    If wsSheet.Visible Then
    wsSheet.Select Replace:=False
    End If
    Next wsSheet
    
    Cells.Replace What:="13 Total", Replacement:="'JUNE 2010", LookAt:=xlPart _
            , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    
    Cells.Replace What:="12 Total", Replacement:="'JUNE 2010", LookAt:=xlPart _
            , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    
    Cells.Replace What:="11 Total", Replacement:="'MAY 2010", LookAt:=xlPart _
            , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    
    Cells.Replace What:="10 Total", Replacement:="'APRIL 2010", LookAt:=xlPart _
            , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    
    Cells.Replace What:="9 Total", Replacement:="'MARCH 2010", LookAt:=xlPart _
            , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    
    Cells.Replace What:="8 Total", Replacement:="'FEB 2010", LookAt:=xlPart _
            , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    
    Cells.Replace What:="7 Total", Replacement:="'JAN 2010", LookAt:=xlPart _
            , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    
    Cells.Replace What:="6 Total", Replacement:="'DEC 2009", LookAt:=xlPart _
            , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    
    Cells.Replace What:="5 Total", Replacement:="'NOV 2009", LookAt:=xlPart _
            , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    
    Cells.Replace What:="4 Total", Replacement:="'OCT 2009", LookAt:=xlPart _
            , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    
    Cells.Replace What:="3 Total", Replacement:="'SEPT 2009", LookAt:=xlPart _
            , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    
    Cells.Replace What:="2 Total", Replacement:="'AUG 2009", LookAt:=xlPart _
            , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    
    Cells.Replace What:="1 Total", Replacement:="'JULY 2009", LookAt:=xlPart _
            , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    
    '
    End Sub
    Last edited by tek9step; 08-20-2010 at 10:54 AM.
    dont play hard just play right !!!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1