Rename Sheet after it is copied and Create Master Workbook

Question

Below is code used to copy sheets from source and then rename and place into a destination.

I would like to extend the functionality to use another cell reference to rename the Sheet Name in the newly created file. (Note each copied workbook will only have one sheet.) Then after all the workbooks are copied, renamed, and sheets renamed, merge all the workbooks in the destination path into one.

Sub CopyRenameFile()
Dim src As String, dst As String, fl As String, f2 As String
Dim rfl As String
Dim rf2 As String

'Source directory
src = Range("B3")

'Destination directory
dst = Range("D3")

'File name
fl = Range("B6")
f2 = Range("B7")

'Rename file
rfl = Range("D6")
rf2 = Range("D7")

On Error Resume Next
    FileCopy src & "\" & fl, dst & "\" & rfl
    FileCopy src & "\" & f2, dst & "\" & rf2
    If Err.Number <> 0 Then
        MsgBox "Copy error: " & src & "\" & rfl
    End If
On Error GoTo 0

Dim xL As Excel.Application
Set xL = New Excel.Application
xL.Visible = True
Dim wb As Excel.Workbook
Set wb = xL.Workbooks.Open(F6)

'In case you don't know how here are two ways to reference a sheet:
Dim sh As Excel.Worksheet
Set sh = xL.Sheets(1)

sh.Name = "TestMeOut"

'Saving and closing are important...
wb.Save
Set wb = Nothing
xL.Quit
Set xL = Nothing

End Sub

i don't see any code copying sheets there. How is this relevant with the question ? — Patrick Lepelletier
The above takes a file f1 from the source directory then renames the file and places it in the destination directory. I would like to add to it to take the newly named file and change the sheet name. After which all files in list are copied and renamed they are then consolidated into a single workbook seperate from the one the macro is being executed from. — Steve Deer

PKatona PKatona · Accepted Answer · 2016-03-21T20:34:54

If it's the active sheet, use

ActiveSheet.Name = "New Name"

If it isn't the active sheet then use:

Sheets("SheetName").Name = "New Name"

or

Sheets(2).Name = "New Name"

for the last one, the index (2 in the example) is the sheet number counting from left to right starting at 1.

To open an Excel workbook by filename:

Dim xL As Excel.Application
Set xL = New Excel.Application
xL.Visible = True
Dim wb as Excel.Workbook
Set wb = xl.Workbooks.Open(put your filename here as a literal or variable)

'In case you don't know how here are two ways to reference a sheet:
Dim sh As Excel.Worksheet
Set sh = xL.Sheets("Sheet1")
'    or
Set sh = xL.Sheets(1)

'put your code here

'Saving and closing are important...
wb.Save
Set wb = Nothing
xL.Quit
Set xL = Nothing

NOTE: to use the Excel references, go to Tool => References and look for the Microsoft Office xx.x Object Library (where xx.x is the version).

Rename Sheet after it is copied and Create Master Workbook

1 Answers