Copying a worksheet into another workbook while changing its name

Question

I have created a workbook which has all the administrative details for a language course. It has several worksheets:

1- course data
2- student data
3- course administration reports
4- student results reports
5- student marks data

The teachers can only have access to the last worksheet: they receive a workbook with that single worksheet which has a summary of their students and course's details, and they input the students' marks throughout the course. At the end, I import those marks back into the original workbook.

I managed to successfully use vba to do the above (and give the workbook a specific name) so that the teachers can have it in their power. (see below an excerpt)

Sheets("NotasProfSem").Select
    Dim filePath As String
        Calculate
    filePath = Range("AP2").Value & Range("AP3").Value & ".xlsx
    Sheets("NotasProfSem").Copy
    ActiveWorkbook.SaveAs Filename:=filePath, _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close

But the student & course information is still linked to the original file. So I devised a work-around: copy worksheet NotasProfSem into the same workbook as NotasProfSem2, turn the links into values, and then copy NotasProfSem2 into a new workbook. But I need to revert the name of the worksheet to its original NotasProfSem.

Question: How can I make the vba code change the name of the worksheet when I copy it into a brand new workbook?

Offering alternative solutions: I'd also be interested in an alternative that allows me to perform "copy" > "paste value" of a group of cells while copying the worksheet into a new workbook. (If it is possible and easy - I am just getting my toes wet on vba).

NOTE: If my question and explanations are not clear, please help me improve them.

When targeting the new workbook, Activesheet.Name="NotasProfSem" — Cyril
So I devised a work-around: copy worksheet NotasProfSem into the same workbook as NotasProfSem2 ... This is not necessary. Copy NotasProfSem to a new workbook, then flatten the formulas (links) on that worksheet directly. (UsedRange.Value = UsedRange.Value may work) — Scott Holtzman
@Cyril - Thanks for your suggestion. It will come in handy in the future. — Teresa

Scott Holtzman Scott Holtzman · Accepted Answer · 2017-09-15T16:23:41

This should do your trick:

Sheets("NotasProfSem").Copy

Dim wbProf as Workbook
Set wbProf = ActiveWorkbook

'remove links and formulas
With wbProf
     'this may not work as is depending on data structure, if not you may need to be more explicit with ranges / cells
    .Sheets(1).UsedRange.Value = wbProf.Sheets(1).UsedRange.Value
    .SaveAs Filename:=filePath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    .Close False
 End

Copying a worksheet into another workbook while changing its name

1 Answers