Open another workbook and copy to sheet

[mrkambo]

i have 2 files
Main.xlsm
Database.xlsx

What i want to do its on Main workbook open, open Database.xlsx and copy it to a sheet in Main called "exceptions" and then close Database.xlsx

Im currently opening Database with:

Workbooks.Open (ActiveWorkbook.Path & "\Database.xlsx")

As its in the same directory

Any guidance would be appreciated

 

[JoeMo]

What part of Database.xlsx do you want to copy - a worksheet, part of a worksheet, all worksheets, ....? What's the name or names of the sheets you want to copy?

 

[mrkambo]

What part of Database.xlsx do you want to copy - a worksheet, part of a worksheet, all worksheets, ....? What's the name or names of the sheets you want to copy?

Its only 1 worksheet, its called sheet1 for now

 

[JoeMo]

Its only 1 worksheet, its called sheet1 for now

Untested so may need some tweaking:

Sub mrkambo()
'run this from the Main workbook
Dim Main As Workbook, DB As Workbook
Set Main = ThisWorkbook
Application.ScreenUpdating = False
Workbooks.Open Filename:=Main.Path & "\Database.xlsx"
Set DB = ActiveWorkbook
DB.Sheets(1).Move after:=Main.Sheets(Sheets.Count)
ActiveSheet.Name = "exceptions"
DB.Close savechanges:=False
'rest of code, if any
Application.ScreenUpdating = True
End Sub

 

[mrkambo]

Excellent this works

However i get a

Run-time error '-2147221080 (900401a8)

Method 'Close of object' _Workbook' failed

But it does close the database workbook

 

[JoeMo]

Excellent this works

However i get a

Run-time error '-2147221080 (900401a8)

Method 'Close of object' _Workbook' failed

But it does close the database workbook

You are welcome - thanks for the reply.

As for the error, I had forgotten you have only one sheet in Database.xlsx.
Remove this line and the error should be gone.

DB.Close savechanges:=False