Excel Macro Help: I'm trying to copy worksheet from another excel file, without renaming the active workbook.

Question

fail macro:

Sub Testing()
Dim currentWorkBook As Workbook
currentWorkBook = ActiveWorkbook

Windows("SharedMacro.xls").Activate
Sheets("FX link").Select
Sheets("FX link").Copy Before:=Workbooks(currentWorkBook). _
Sheets(1)
End Sub

Answer 1

Well first you will have to have the other file already open or set up the macro to open the other file. But here's how I'd try to do it.

If the SharedMacro.xls file isn't open then do this:

Dim currentWorkbook As Workbook

Set currentWorkBook = ActiveWorkbook

Workbooks.Open Filename:="SharedMacro.xls"

Workbooks("SharedMacro.xls"). _
Sheets("FX link").Copy Before:= _
currentWorkBook.Sheets(1)

If the SharedMacro workbook is open, then all you need is:

Workbooks("SharedMacro.xls"). _
Sheets("FX link").Copy Before:= _
ActiveWorkbook.Sheets(1)

Notice the two underscores denoting line continuations on the second example. The macro you were trying to write can all be written in one line.

This would copy the sheet "FX link" from SharedMacro.xls and paste a copy of it before the first sheet in you activeworkbook. Note that when opening the file you might want to specify the entire file path-name. Sometimes you can get by with just specifying the workbook's name.

You don't need to do all of the activating and selecting that you do in your macro. You could cut several lines out of your macro that aren't needed. The reason your macro isn't working though is due to the following line:

currentWorkBook = ActiveWorkbook

Anything that is a visual object, such as a workbook, worksheet, range, etc have to use the Set value to set the two equal to each other. If you are just refering to values and not objects you only need the equals sign. That line should instead be:

Set currentWorkBook = ActiveWorkbook

Answer 2

Excel Macro Copy Worksheet