Unable to select a sheet while copy pasting data

[Pranesh]

Hi All,

Im trying to copy a data from 1st excel to 2nd excel. While im about to select a sheet in 2nd file im getting "Subscript out of range" as error. I have used the same code earlier for several other excels but I haven't get this kind of error. Can someone help me to fix this. Below is my code.

Im getting error exactly here "Sheets("Addition").Select"

Sub Template()
Application.ScreenUpdating = False
Dim twb As Workbook
Dim tw As ThisWorkbook

Set tw = ThisWorkbook
Sheet3.Select
Range("F10").Select
TempSBU = Selection.Value
Workbooks.Open Filename:=TempSBU
Set twb = ActiveWorkbook
tw.Activate
Sheet4.Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
twb.Activate
Sheets("Addition").Select

 

[Fluff]

Probably means that you don't have a sheet called "Addition" in the workbook that was opened by the code.

 

[Pranesh]

Probably means that you don't have a sheet called "Addition" in the workbook that was opened by the code.

Hi Fluff - I have a sheet called "Addition" and also tried to call it out as sheet1.select but getting the same error.

 

[Fluff]

Double check the spelling, including any leading/trailing spaces.
You cannot use Sheet1.Select as you can only use the codename like that for sheets in ThisWorkbook

 

[Pranesh]

Double check the spelling, including any leading/trailing spaces.
You cannot use Sheet1.Select as you can only use the codename like that for sheets in ThisWorkbook

Thanks Fluff.. There was a space at the end and that is why it didn't work. To avoid this confusion can you please let me know how to use Sheet1.Select

 

[Fluff]

You can only use the sheet codenames in that manner for sheets in the workbook containing the code.