Renaming worksheet not working

[ferocious12]

Hi All,

I am copying activeworskeet in all the files in a certain folder. I want to rename the copied worksheet in the destination workbooks but it is not working.

Can you please help where I am making a mistake?

This is the code line:

ActiveWorkbook.Sheets("Actual Receipts*").Name = "Actual Receipts"

Many thanks

Public Sub CopySheetToAllWorkbooksInFolder()

Dim sourceSheet As Worksheet
Dim folder As String, filename As String
Dim destinationWorkbook As Workbook

'Worksheet in active workbook to be copied as a new sheet to the existing workbooks

Set sourceSheet = ActiveWorkbook.Worksheets("Actual Receipts")

On Error Resume Next


'Folder containing the existing workbooks

folder = "C:\Users\xxx\Downloads\macro\Destination files\"

filename = Dir(folder & "*.xls", vbNormal)
While Len(filename) <> 0
Debug.Print folder & filename
Set destinationWorkbook = Workbooks.Open(folder & filename)
sourceSheet.Copy before:=destinationWorkbook.Sheets(1)

ActiveWorkbook.Sheets("Actual Receipts").Delete
ActiveWorkbook.Sheets("Actual Receipts*").Name = "Actual Receipts"


 
destinationWorkbook.Close True
filename = Dir() ' Get next matching file
Wend

End Sub

 

[Marc L]

Once the worksheet is copied, the new worksheet is the active sheet, isn't it ?​

At very beginner level just using the ActiveSheet statement …​

​

And the obviously obvious is to avoid to delete it just before to rename it ‼ ​

​

Again all logic errors can be find out just removing the useless On Error Resume Next codeline …​

​

 

[ferocious12]

Once the worksheet is copied, the new worksheet is the active sheet, isn't it ?​

At very beginner level just using the ActiveSheet statement …​

​

And the obviously obvious is to avoid to delete it just before to rename it ‼ ​

​

Again all logic errors can be find out just removing the useless On Error Resume Next codeline …​

​

Thanks