Macro Button to move 2 tabs to a new workbook

**tmleynek** · 11-13-2015, 02:54 PM

Hi All,

I've went through the website and have found numerous threads in regards to this, but none seem to be working specifically for what I'm trying to accomplish.

I have a workbook with approximately 50 tabs and various macros throughout the workbook. I have two tabs (Billing Sheet & Review Tab) that have references to other tabs that I need to be able to extract and save to desktop as a new name based on cells in the inputs tab --- Basically "Billing + Review Sheet for Inputs!e23"

I would like a button on both the billing and review tabs that would initiate this macro. I also would like to keep the formatting, while getting rid of the references (basically a copy/paste without formulas). The review tab has a bunch (60) checkboxes that also need to be exported. I would like to keep the macros from the workbook to the new workbook, because I also have a "Print to PDF" macro that I would like to still work.

What I currently have -- just essentially selects the two sheets and then saves the entire worksheet.

Sub SaveExcelDesktop()

    Dim DeskString As String
    
    Worksheets(Array("Billing", "Review Sheet")).Select

    DeskString = CreateObject("WScript.Shell").specialfolders("Desktop")

    ActiveSheet.SaveAs Filename:=DeskString & "\" & "Review+Billing of " & Sheets("Inputs").Range("e82").Value & " - " & Sheets("Inputs").Range("e89").Value

End Sub

Any help would be appreciated.

Thanks!!
Travis

**AlphaFrog** · 11-13-2015, 03:45 PM

Try this. It saves a copy of the workbook. Then opens it and deletes the sheets you don't want to keep.

Sub SaveExcelDesktop()
    
    Dim DeskString As String, ws As Worksheet
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    DeskString = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & _
        "Review+Billing of " & Sheets("Inputs").Range("E82").Value & " - " & Sheets("Inputs").Range("E89").Value & _
        "." & Split(ThisWorkbook.Name, ".")(1)
    
    ThisWorkbook.SaveCopyAs DeskString
    
    With Workbooks.Open(DeskString)
        
        .Sheets("Billing").UsedRange.Value = .Sheets("Billing").UsedRange.Value
        .Sheets("Review Sheet").UsedRange.Value = .Sheets("Review Sheet").UsedRange.Value
    
        For Each ws In .Sheets
            If ws.Name <> "Billing" And ws.Name <> "Review Sheet" Then ws.Delete
        Next ws
        
        .Close SaveChanges:=True
        
    End With
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    MsgBox DeskString, vbInformation, "Billing and Review Copy Saved"
    
End Sub

**tmleynek** · 11-13-2015, 06:55 PM

I'm getting a 400 error. The workbook is created, but tabs don't delete.

**AlphaFrog** · 11-13-2015, 07:18 PM

I think this should work. There may be something additional about your workbook that hasn't been described.

Do you have an auto-open macro that automatically runs when the file is opened?

Do you have a userform in the workbook? And if yes, is it displayed or triggered automatically?

Are the two sheets password protected or hidden?

Does the code replace the formulas with values on both sheets before it errors?

**tmleynek** · 11-16-2015, 11:55 AM

Almost yes to all of those. I have a userform when the workbook opens for the first time (referenced by a specific cell in the inputs tab). This userform dumps data into the inputs tab.

Private Sub Workbook_Open()
    With ActiveWorkbook
        If Sheets("Inputs").Range("E5") = "" Then
            UserForm1.Show
        End If
    End With
End Sub

The two sheets are also protected (without a password).

The code does replace the formulas with values on both sheets before it errors.

Macro Button to move 2 tabs to a new workbook

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro Button to move 2 tabs to a new workbook

Re: Macro Button to move 2 tabs to a new workbook

Re: Macro Button to move 2 tabs to a new workbook

Re: Macro Button to move 2 tabs to a new workbook

Re: Macro Button to move 2 tabs to a new workbook

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] need coding for macro to move related data from 2 different tabs into a 3rd tab

Macro to loop through and move data to new tabs

Macro to move lines between tabs

[SOLVED] How do I move the worksheet tabs to the top of the workbook?

Using a Button Object to move a sheet into a new Workbook

How do I use the keyboard to move between tabs in a workbook?

[SOLVED] command button move worksheet from one workbook to another

sort tabs in a workbook other than one at a time with move or cop.

Bookmarks

Bookmarks

Posting Permissions