Copy specific worksheets to new workbook (as values). Save as

[jwb1012]

Hello, I am attempting to do the following:

1. Copy all worksheets whose name ends in "-PP" into a new workbook (formats, values - NO formulas)
2. Open a pop-up box in the new workbook that asks the user to enter the version number
3. Open the save as window and save the file to the user's desktop as "[text in cell $A$1 of 'Setup' worksheet]_Version [number entered in pop-up]"

Note: this file will be used by many people, so need to make sure the "save to desktop" will work for all users.

Here is the code I've got thus far, but it copies all worksheets ending in "-PP" to separate worksheets. It also doesn't copy as values and has no "saving functionality".

Any thoughts? Thank you in advance for any guidance.

Sub Copy_Sheets()
    Dim Sh As Worksheet
    
        On Error Resume Next
            For Each Sh In ActiveWorkbook.Sheets
                If Right(Sh.Name, 3) = "-PP" Then
                    Sh.Copy
                End If
            Next Sh
        On Error GoTo 0
        
End Sub

 

[JLGWhiz]

See if this works for you\

Sub Copy_Sheets()
    Dim Sh As Worksheet, wb As Workbook
    Set wb = Workbooks.Add
        On Error Resume Next
            For Each Sh In ThisWorkbook.Sheets
                If Right(Sh.Name, 3) = "-PP" Then
                    Sh.Copy After:=wb.Sheets(wb.Sheets.Count)
                End If
            Next Sh
        On Error GoTo 0
End Sub

 

[jwb1012]

Hmm so this accomplishes the general task of opening them in one workbook opposed to 7 separate workbooks, but:

• Biggest issue: the formulas are in-tact still. I need everything to be values.

Also, steps 2-3 aren't addressed in this code. Is this not possible?

1. Copy all worksheets whose name ends in "-PP" into a new workbook (formats, values - NO formulas)
2. Open a pop-up box in the new workbook that asks the user to enter the version number
3. Open the save as window and save the file to the user's desktop as "[text in cell $A$1 of 'Setup' worksheet]_Version [number entered in pop-up]"

 

[JLGWhiz]

Nope, I did not address them because I thought the multiple workbooks was you only hang up. Try this code.

Sub Copy_Sheets()
    Dim Sh As Worksheet, wb As Workbook, txt As String
    Set wb = Workbooks.Add
        On Error Resume Next
            For Each Sh In ThisWorkbook.Sheets
                If Right(Sh.Name, 3) = "-PP" Then
                    Sh.Copy After:=wb.Sheets(wb.Sheets.Count)
                End If
            Next Sh
        On Error GoTo 0
    txt = InputBox("Enter a version number", "VERSION")
    wb.SaveAs ThisWorkbook.Sheets("Setup").Range("A1").Value & "_" & txt & ".xlsx"
End Sub

 

[jwb1012]

Wow! That is incredible! I have one last follow-up for you, but this may push my luck...

The first worksheet in the new workbook is blank and is named "Sheet 1"... is it possible to just rename this worksheet from "Sheet 1" ... to ... "<<< Import" ?

Thanks again for the help thus far!

 

[JLGWhiz]

Wow! That is incredible! I have one last follow-up for you, but this may push my luck...

The first worksheet in the new workbook is blank and is named "Sheet 1"... is it possible to just rename this worksheet from "Sheet 1" ... to ... "<<< Import" ?

Thanks again for the help thus far!

Sub Copy_Sheets()
    Dim Sh As Worksheet, wb As Workbook, txt As String
    Set wb = Workbooks.Add
    [COLOR=#ff8c00]wb.Sheets(1).Name = "Import"[/COLOR]<import"< font=""><import"< font="">
        On Error Resume Next
            For Each Sh In ThisWorkbook.Sheets
                If Right(Sh.Name, 3) = "-PP" Then
                    Sh.Copy After:=wb.Sheets(wb.Sheets.Count)
                End If
            Next Sh
        On Error GoTo 0
    txt = InputBox("Enter a version number", "VERSION")
    wb.SaveAs ThisWorkbook.Sheets("Setup").Range("A1").Value & "_" & txt & ".xlsx"
End Sub

If you want the Less Than symbols in, you will need to do it manually. They screw up the text if included here.</import"<></import"<>

 

[jwb1012]

Thanks again!

Can you elaborate on what you mean when you say "If you want the Less Than symbols in, you will need to do it manually. They screw up the text if included here." I gave it a shot with the "<<" and it appears to work for me.

Also, is there a way to close the new workbook and do NOT save if the user hits "CANCEL" on the input form?

 

[JLGWhiz]

Thanks again!

Can you elaborate on what you mean when you say "If you want the Less Than symbols in, you will need to do it manually. They screw up the text if included here." I gave it a shot with the "<<" and it appears to work for me.

Also, is there a way to close the new workbook and do NOT save if the user hits "CANCEL" on the input form?

it was deleting part of the code when I put the three symbols in. Don't know what the difference is, but I didn't want to fool with it any more since the rest of the code was working. Add this line of code

If txt = "" Then
    wb.Close False
End If

to close without saving and avoid alerts.