Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: select all sheets with "Aug" in the name and move them to another workbook

  1. #1

    select all sheets with "Aug" in the name and move them to another workbook



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am trying to sort multiple sheets which are added to the workbook daily by date and time.
    Each month I wish to remove the previous months sheets and place them in order within a workbook for the whole year.
    I have put together a code which will sort "Aug" from " Sep", selecting all the Sep sheet tabs. I can not figure out how to move them to the other book. I will attach a sample sending and receive workbooks. The code I have so far is in the "thisworkbook"
    of the vba.

    Code:
    Sub Test()
        Dim blnReplace As Boolean
        Dim sht As Object
    .
     
        blnReplace = True
        For Each ws In Sheets
            If InStr(ws.Name, "Aug") = 0 Then
          
                ws.Select blnReplace
                blnReplace = False
       ' this next part of the code is ment to move the selected sheets but does not work
       ' module1 was an attempt to record a macro but it names the sheets in the array which
      ' will change all the time.
       
      '   MyObject.Move after:=Workbooks("receive.xlsm").Sheets("start")
        
            End If
           
        Next
    
     '   ActiveWorkbook.Save
      '  ActiveWindow.Close
       ' Range("C8").Select
    End Sub
    Attached Files Attached Files

  2. #2
    Code:
    Sub Test()   
    Dim ws As Worksheet
    
        For Each ws In ThisWorkbook.Worksheets
        
            If InStr(ws.Name, "Aug") = 0 Then
          
                ws.Move after:=Workbooks("receive.xlsm").Sheets("start")
            End If
        Next
    
    
     '   ActiveWorkbook.Save
      '  ActiveWindow.Close
       ' Range("C8").Select
    End Sub

  3. #3
    Wow, that was fast and it works too.
    Thanks Russ

  4. #4
    I don't see how to mark this as solved and I will post a link on excelkey to this solution. Thanks again
    Russ

  5. #5
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    375
    Articles
    0
    Excel Version
    2020
    I'd use:

    Code:
    Sub M_snb()
       For Each sh In Sheets
          c00 = c00 & "|" & sh.Name
       Next
    
       Sheets(Filter(Split(c00, "|"), "Aug")).Move ,Workbooks("receive.xlsm").Sheets("start")
    End Sub

  6. #6
    I have posted a link at excelkey.com for your solution.
    I don't see how to change he post to solved or I would.
    Thanks again
    Russ

  7. #7
    Thanks SNB, this actually works better because it moves what I ask for...Aug where as my other code selects everything that is not Aug to send.
    Russ

  8. #8
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    375
    Articles
    0
    Excel Version
    2020
    Since I assume you will do this after each month you could make the macro more robust using:


    Code:
    Sub M_snb()    
       For Each sh In Sheets
          c00 = c00 & "|" & sh.Name
       Next
    
       Sheets(Filter(Split(c00, "|"), Application.GetCustomListContents(3)(Month(Date) - 1))).Move ,Workbooks("receive.xlsm").Sheets("start")
     End Sub

  9. #9
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    Office 365
    Just be aware that "for each sheet" changes the number of sheets so it will fail with the last ones.

    say you start off with 10 sheets, and move 3. In effect for each will only check 7 and will NOT check the last three

    rather use for i = sheets.count to 1 step -1

  10. #10
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    375
    Articles
    0
    Excel Version
    2020
    @WoO

    You are mistaken.

Page 1 of 2 1 2 LastLast

Posting Permissions

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