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

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
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.

Code:
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

Well-known Member
Joined
Feb 7, 2012
Messages
11,311
Office Version
2013
Platform
Windows
See if this works for you\

Code:
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
 
Last edited:

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
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

Well-known Member
Joined
Feb 7, 2012
Messages
11,311
Office Version
2013
Platform
Windows
Nope, I did not address them because I thought the multiple workbooks was you only hang up. Try this code.

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

Board Regular
Joined
Oct 17, 2016
Messages
167
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

Well-known Member
Joined
Feb 7, 2012
Messages
11,311
Office Version
2013
Platform
Windows
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!
Code:
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"<>
 
Last edited:

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
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

Well-known Member
Joined
Feb 7, 2012
Messages
11,311
Office Version
2013
Platform
Windows
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
Code:
If txt = "" Then
    wb.Close False
End If
to close without saving and avoid alerts.
 
Last edited:

Forum statistics

Threads
1,082,587
Messages
5,366,486
Members
400,894
Latest member
frog9000

Some videos you may like

This Week's Hot Topics

Top