Using Macro to import sheet into active workbook

[lysander]

I would like my macro that I'm running to copy a sheet to the beginning of my active workbook from another open workbook.

I'm using the below code but having problems with the second line:

Windows("IC MACRO for WIP v.1a.xlsb").Activate
Sheets("SUMMARY TEMPLATE").Copy Before:=Workbooks("IC WIP 12.13.17test.xls").Sheets("IC_WIP")


For info: IC_WIP sheet is sheet 1 in my active workbook where I want the sheet to be copied to.

What am I doing wrong?

Thanks

 

[JoeMo]

I would like my macro that I'm running to copy a sheet to the beginning of my active workbook from another open workbook.

I'm using the below code but having problems with the second line:

Windows("IC MACRO for WIP v.1a.xlsb").Activate
Sheets("SUMMARY TEMPLATE").Copy Before:=Workbooks("IC WIP 12.13.17test.xls").Sheets("IC_WIP")


For info: IC_WIP sheet is sheet 1 in my active workbook where I want the sheet to be copied to.

What am I doing wrong?

Thanks

Tell us what exactly are the problems you are having with the second line.

 

[Norie]

Does this work?

Workbooks("IC MACRO for WIP v.1a.xlsb").Sheets("SUMMARY TEMPLATE").Copy Before:=Workbooks("IC WIP 12.13.17test.xls").Sheets("IC_WIP")

Or this?

Workbooks("IC MACRO for WIP v.1a.xlsb").Sheets("SUMMARY TEMPLATE").Copy Before:=ActiveWorkbook.Sheets("IC_WIP")

Or this?

Workbooks("IC MACRO for WIP v.1a.xlsb").Sheets("SUMMARY TEMPLATE").Copy Before:=ThisWorkbook.Sheets("IC_WIP")

 

[lysander]

Hi again

for the first two codes I'm told:
Run-time error '1004' Excel cannot insert these sheets into the destination workbook, because it contains fewer rows and columns that the source workbook...

for the last code it says Run-time error 9 - subscript out of range

 

[Norie]

The first two indicates that the 2 workbooks are in the same format, the third that you are running the code from another, third, workbook.

What is it you want from the sheet you are trying to copy?

 

[JoeMo]

The first two errors result because the destination workbook is from an Excel version earlier than 2007 while the sheet you are trying to copy is from Excel 2007 or a later version. If that's what you want, adjust your code to copy only the used range of the source sheet (assuming it does not go beyond the row/column count bounds of the earlier version). and paste to a new sheet in the destination workbook, then give that new sheet the same name as the source sheet.

 

[lysander]

Thank you for this info I would still be going nuts without it.
Yes I thought about a copy of the ranges needed only but I wanted to bring over some functions as well in the worksheet. If I can't bring the functions over with the worksheet my custom formulas e.g. as follows won't work:
=LastSavedTimeStamp()
=CountColour(PSI Follow-Up!$P$2:$P$200,$L$2)

How can I get around this?
Thanks

[TABLE="width: 77"]
<colgroup><col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3766;"> <tbody>[TR]
[TD="class: xl63, width: 103, bgcolor: transparent, align: right"][/TD]
[/TR]
</tbody>[/TABLE]


How else could I have the functions appear in active workbook?

 

[JoeMo]

Thank you for this info I would still be going nuts without it.
Yes I thought about a copy of the ranges needed only but I wanted to bring over some functions as well in the worksheet. If I can't bring the functions over with the worksheet my custom formulas e.g. as follows won't work:
=LastSavedTimeStamp()
=CountColour(PSI Follow-Up!$P$2:$P$200,$L$2)

How can I get around this?
Thanks

[TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl63, width: 103, bgcolor: transparent, align: right"][/TD]
[/TR]
</tbody>[/TABLE]


How else could I have the functions appear in active workbook?

Assuming those custom functions are UDFs, you can export them from the source workbook and import them to the destination workbook. Another approach, if acceptable, would be to convert the destination workbook to the Excel 2007 and later versions format. You can do that by opening the destination workbook in Excel 2007 or later than File>Info>Convert and follow the prompts. Verify that the converted workbook has the increased row and column counts and use it to copy the full source sheet and associated code.

 

[lysander]

Importing from source to destination is what I would like to do. I can do this Manually very easily. However I'm not sure how to get the Macro to import the functions to the new workbook so they continue to work for the sheet that has been copied and brought into the new workbook.

 

[JoeMo]

Importing from source to destination is what I would like to do. I can do this Manually very easily. However I'm not sure how to get the Macro to import the functions to the new workbook so they continue to work for the sheet that has been copied and brought into the new workbook.

Try this:

1. Export the custom function(s) you want to use in the destination workbook (say its DestinationBook.xlsm) to .bas files that are saved to the file folder the Source workbook (say its SourceBook.xlsm) is in. In the example here the exported file is named "SourceCode.bas".
2. With both Sourcebook and DestinationBook open, run the code below. This should import the .bas file into the DestinationBook's VBProject.
3. Repeat 1. & 2. for additional custom functions in the SourceBook you wish to import to the DestinationBook.

Sub ImportMyUDF()
'Assumes the source code to be imported is in same file folder as the Source Workbook in .bas file
With Workbooks("DestinationBook.xlsm")
    .VBProject.VBComponents.Import ThisWorkbook.Path & "\" & "SourceCode.bas"
End With
End Sub