Copy a sheet from a closed workbook and paste on an existing sheet of this workbook

[Nelson78]

Hello everybody.

I need to copy the sheet ("Alpha") from the closed workbook ("Beta") and paste it in thisworkbook sheet ("Alpha").

Sub copy_alpha()
     Application.ScreenUpdating = False
     Set wb1 = thisworkbook
     Set file2 = Workbooks.Open("S:\xxxx\yyyyyy\Beta.xls")
     file2.Sheets("Alpha").Copy wb1.Sheets("Alpha")
     file2.Close
     Application.ScreenUpdating = True
End Sub

Unfortunately, with this code a new sheet is generated in thisworkbook ("Alpha(2)"), instead of pasting in the pre existing sheet Alpha.

Could anyone help me?

 

[Yongle]

Sub copy_alpha()
     Dim wb1 As Workbook, file2 As Workbook
     Application.ScreenUpdating = False
     Set wb1 = ThisWorkbook
     Set file2 = Workbooks.Open("[I][COLOR=#000080]C:\folder\subfolder\filename.xlsx[/COLOR][/I]")
     file2.Sheets("Alpha").Copy After:=wb1.Sheets(wb1.Sheets.Count)
     file2.Close False
     Application.ScreenUpdating = True
End Sub

 

[Fluff]

Maybe

Sub copy_alpha()
     Application.ScreenUpdating = False
     Set WB1 = ThisWorkbook
     Set File2 = Workbooks.Open("S:\xxxx\yyyyyy\Beta.xls")
     File2.Sheets("Alpha").UsedRange.Copy WB1.Sheets("Alpha").Range("A" & Rows.Count).End(xlUp).Offset(1)
     File2.Close
     Application.ScreenUpdating = True
End Sub

 

[Yongle]

@Nelson78 - it would be best if you ignore my suggestion

 

[Nelson78]

It's all clear, except the text highlighted in red.

Maybe

     File2.Sheets("Alpha").UsedRange.Copy WB1.Sheets("Alpha").Range("A" & Rows.Count).End(xlUp).[COLOR="#FF0000"]Offset(1)[/COLOR]

 

[Fluff]

The part in red simply moves down 1 row.

 

[Nelson78]

The part in red simply moves down 1 row.

Yes, infact I have removed it.

I was wondering why you inserted that instruction.
Maybe in order to preserve the header?

 

[Fluff]

I was wondering why you inserted that instruction.
Maybe in order to preserve the header?

Yes, I wasn't sure if you already had data in that sheet.