Excel Macro Errors!

[smitmika99]

I have a master workbook called Paterson Bulk Service Records and then individual sheets in folders for each of the Master workbook sheets to link over (i've done it before for something else and I had no trouble but now i do)

The bold line is the one that has a Subscript Error and changes the individual sheet to 1(2) and it's just a pain! Someone please help me!

Sub wscopy()



Dim PatersonBulkServiceRecords, fleet1 As Workbook, ws11 As Worksheet

Set PatersonBulkServiceRecords = ThisWorkbook
Set fleet1 = Workbooks.Open("E:\Paterson Bulk Service Records.xlsx")
Set ws11 = PatersonBulkServiceRecords.Sheets("1")
Set ws21 = fleet1.Sheets("1")



ws21.Copy before:=ws11
fleet1.Close


Application.DisplayAlerts = False
ws11.Delete
Application.DisplayAlerts = True


PatersonBulkServiceRecords.Sheets("1(2)").Name = "1"
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Hi & welcome to MrExcel
You need a space in the sheet name

PatersonBulkServiceRecords.Sheets("1 (2)").name = "1"

 

[smitmika99]

Hi & welcome to MrExcel
You need a space in the sheet name

PatersonBulkServiceRecords.Sheets("1 (2)").name = "1"

It comes back with "compile error end of statement" if I put a space in the sheet name..

 

[Fluff]

Not for me it doesn't. Have you made any other changes to the code you've posted?