Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: Moving and saving sheets between workbooks using Code Name & Sheet Name

  1. #11
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,808
    Post Thanks / Like
    Mentioned
    383 Post(s)
    Tagged
    41 Thread(s)

    Default Re: Moving and saving sheets between workbooks using Code Name & Sheet Name

    How about
    Code:
          With ActiveWorkbook
             .SaveAs .path & "\" & Ary(i).name, 51
             .Close
          End With
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  2. #12
    New Member
    Join Date
    Sep 2018
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Moving and saving sheets between workbooks using Code Name & Sheet Name

    Still i = 0 error I'm afraid

  3. #13
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,808
    Post Thanks / Like
    Mentioned
    383 Post(s)
    Tagged
    41 Thread(s)

    Default Re: Moving and saving sheets between workbooks using Code Name & Sheet Name

    In that case what is the actual sheet name of sheet6?
    Also does it move that sheet to a new workbook?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  4. #14
    New Member
    Join Date
    Sep 2018
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Moving and saving sheets between workbooks using Code Name & Sheet Name

    Yes, Sheet6 moves to a new sheet before the error kicks in.

    Sheet6 is called "CWDP_HH_1205_Allocate_01"

    That name changes every week based upon date change
    Last edited by horizonflame; May 13th, 2019 at 03:19 PM.

  5. #15
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,808
    Post Thanks / Like
    Mentioned
    383 Post(s)
    Tagged
    41 Thread(s)

    Default Re: Moving and saving sheets between workbooks using Code Name & Sheet Name

    Ok, this should work
    Code:
    Sub horizonflame()
       Dim Ary As Variant
       Dim i As Long
       Dim Fname As String
       
       Ary = Array(Sheet6, Sheet7, Sheet8, Sheet9, Sheet10, Sheet11, Sheet12, Sheet13, Sheet14, Sheet15, Sheet16, Sheet17, Sheet18, Sheet19, Sheet20, Sheet21, Sheet22)
       For i = 0 To UBound(Ary)
          Fname = Ary(i).Name
          Ary(i).Move
          With ActiveWorkbook
             .SaveAs ThisWorkbook.path & "\" & Fname, 51
             .Close
          End With
       Next i
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  6. #16
    New Member
    Join Date
    Sep 2018
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Moving and saving sheets between workbooks using Code Name & Sheet Name

    Yes! Thank you so much for your help :D

  7. #17
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,808
    Post Thanks / Like
    Mentioned
    383 Post(s)
    Tagged
    41 Thread(s)

    Default Re: Moving and saving sheets between workbooks using Code Name & Sheet Name

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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