asked on

Copy Tab to different Workbook

I have a workbook called cover.xlsx that has a tab called "cover". I want to be able to call that file and copy the tab to the activeworkbook. I aslo want it to be the first sheet. I would like a macro to do this. Any help would be greatly apperciated.

Thanks,
Montrof

Farzad Akbarnejad

Hi,
Try the following:

Sub MacroProc()
   Set wb = Workbooks.Open("cover.xlsx")
   wb.Sheets("cover").Copy before:=ThisWorkbook.Sheets("Sheet1")
   wb.Close
End Sub

Open in new window

-FA

montrof

ASKER

This is very close the only issue i am having is that I am running the code from a different workbook. So I want the sheet to be added to the active workbook and it is adding to the workbook that the code is in.

Thanks,
Montrof

Farzad Akbarnejad

You can choose your destination workbook as follow:

Sub MacroProc()
   set wbTarget = Workbooks.Open("path_to_your_target_workbook\your_destination.xlsx")
   Set wb = Workbooks.Open("cover.xlsx")
   wb.Sheets("cover").Copy before:=wbTarget.Sheets("Sheet1")
   wb.Close
   wbTarget.Close
End Sub

Open in new window

-FA

montrof

ASKER

The problem is I have to run this on several workbooks so I did not want to have to hard code the Path.

Thanks,
Montrof

ASKER CERTIFIED SOLUTION

Farzad Akbarnejad

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

montrof

ASKER

Thank you