Stand-alone macro not working when added in "Add-in"

[mortu90]

Hi everybody,

I want to say that my experience with VBA is average. I cannot build full codes by myself, but I can modify most of the available temaplates
for my own purposes. But lets jump straight to my (weird) question:

I have developed a macro code which works absolutely fine from the workbook itself, but when I try to make it global (save it as an "add-in") I receive all sorts of errors. I believe that new "add-in" file has already a default "Sheet1" which perhaps messes up with my formulas. Besides that I have absolutely no clue why it works one way and not the other.


For Example:

Sub Copy_Header()
    ActiveSheet.Name = "Sheet1"
    Range("A1").EntireRow.Insert


    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual


    Dim wbCopy As Workbook
    Dim wsCopy As Worksheet
    Dim rngCopy As Range
    Dim wbPaste As Workbook
    Dim wsPaste As Worksheet
    Dim rngPaste As Range
    
    Set wbCopy = Workbooks.Open("Y:\Carriers\UPS\Header.xls")    'change path
    Set wsCopy = wbCopy.Worksheets("Header")
    Set rngCopy = wsCopy.Range("a1:ip1")
    Set wbPaste = ThisWorkbook
    Set wsPaste = wbPaste.Worksheets("Sheet1")  'paste to different sheet?
    Set rngPaste = wsPaste.Range("a1")  'change this if needed
    
    rngCopy.Copy
    rngPaste.PasteSpecial
    
    Application.DisplayAlerts = False
    
    Workbooks("Header.xls").Close


End Sub

The above macro (part of the full vba code) simply copy pastes a header from one document to another, which again works fine alone. When I try to make it global it simply doesn't.

Any input is much appreciated.

Regards,
M.

 

[RoryA]

ThisWorkbook refers to the workbook containing the code, which is now your add-in, and I assume that is not where you want to paste to.

 

[mortu90]

Thanks for you reply RoryA! I already changed it from "ThisWorkBook" to "ActiveWorkBook". Perhaps I can trigger the right workbook in a different way. Also, whenever I add it as an "add-in" excel auto-creates a new worksheet named by default "sheet1".

The annoying part is that I already made it work, but I cannot share it with anybody as an add-in.

 

[RoryA]

I don't follow exactly what your problem is anymore?

 

[mortu90]

Hi RoryA,

Sorry for being a bit too vague. The problem was indeed as you suggested with the Workbook numbers. I didn't take into account that by adding a new "add-in" a new & hidden workbook is added. This eventually messed the other references.

Everything is solved now!

Thanks a lot for your help!