Results 1 to 6 of 6

Thread: Help with mutliple Worksheets copy and save as

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

    Default Help with mutliple Worksheets copy and save as

    Hi All,

    Last question to finish my recent project :D

    I need to perform the following functions:

    - Copy Sheet2, Sheet3 and Sheet 4 to new workbook (using codename as sheet name change weekly)
    - Hide Sheet2
    - Save workbook in original directory taking Sheet2 name and fixed suffix
    - Remain/return to original workbook

    This is what I have tried to put together so far...

    Code:
    Application.ScreenUpdating = False
    
    
    Sheets(Array(Sheet2, Sheet3, Sheet4)).Move
    
    Sheet2.Visible = False
    
    ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & Sheet2.Name & "_Error_Corrections" & ".xlsx"
    
    
    Application.ScreenUpdating = True
    Thank you
    Last edited by horizonflame; May 17th, 2019 at 01:57 PM.

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

    Default Re: Help with mutliple Worksheets copy and save as

    Try
    Code:
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Sheet2.Name & "_Error_Corrections" & ".xlsx",51
    - 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

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

    Default Re: Help with mutliple Worksheets copy and save as

    Hi @Fluff, thanks for the quick reply again. I have two errors when I run the code:

    Code:
    Sheets(Array(Sheet2, Sheet3, Sheet4)).Move
    Run-time error '13': Type mismatch
    This is me not referencing my sheets correctly?

    Code:
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Sheet2.Name & "_Error_Corrections" & ".xlsx",51
    Compile error: Expected: name parameter

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

    Default Re: Help with mutliple Worksheets copy and save as

    Try
    Code:
    Sub horizonflame()
       Dim Fname As String
       Fname = Sheet2.Name
       Sheets(Array(Sheet2.Name, Sheet3.Name, Sheet4.Name)).Move
       Sheets(Fname).Visible = False
       ActiveWorkbook.SaveAs ThisWorkbook.path & "\" & Fname & "_Error_Corrections" & ".xlsx", 51
    End Sub
    Last edited by Fluff; May 17th, 2019 at 02:48 PM.
    - 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

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

    Default Re: Help with mutliple Worksheets copy and save as

    That is spot on, thanks again.

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

    Default Re: Help with mutliple Worksheets copy and save as

    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
  •