• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 648
  • Last Modified:

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
0
montrof
Asked:
montrof
  • 3
  • 3
1 Solution
 
Farzad AkbarnejadDeveloperCommented:
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
0
 
montrofAuthor Commented:
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
0
 
Farzad AkbarnejadDeveloperCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
montrofAuthor Commented:
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
0
 
Farzad AkbarnejadDeveloperCommented:
You can choose path and pass it as parameter:

Sub MacroProc()
   Dim sPath as String
   sPath = ""     ' Choose it as way as you want. You can chosse by help of dialog box or input box

   sPath = InputBox("Enter your path?")

   set wbTarget = Workbooks.Open(sPath)
   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
0
 
montrofAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now