Code not working as `Add-In or Personal XLSB` [The same code works fine in workbook where it was created]

Question

Below code is working fine and doing what I want to do. But when I use it as Add-In to run on all other workbooks it says subscript out of range.

There may be object related confusion or the add-in gets confused which workbook to refer.

I'm new to vba and request all of you to help.

 Sub mac_3()
 Dim xlsname As String


Dim d As VbMsgBoxResult: d = MsgBox("Would you like to add record?" & vbNewLine & vbNewLine & "(Esc/Cancel to add something.)", vbYesNoCancel + vbQuestion, "Details!")
If d = vbNo Then
    Sheets("MPSA").Range("a13").Value = "Record is not available."
    Sheets("MPSA").Range("a13").Font.Bold = True
    ActiveWorkbook.Save
    GoTo savefile
    Exit Sub
End If
If d = vbCancel Then
    Dim myValue As Variant
    myValue = Application.InputBox("Non-Transactional number!", "Please paste number[separate with comma ,]:")
    If myValue = False Then
    Exit Sub
    Else
    Sheets("MPSA").Range("a13").Value = "Dataot available for : " & myValue
    Sheets("MPSA").Range("a13").Font.Bold = True
    ActiveWorkbook.Save
    GoTo savefile
    Exit Sub
    End If
End If

On Error GoTo Cleanup
Application.DisplayAlerts = False: Application.EnableEvents = False: Application.ScreenUpdating = False

Sheets("MPSA").Range("a14:ac14").Value = Array( _
"ACCOUNT NAME", " ACCOUNT NUMBER", "AGE", "ENTITY NAME", "GROUP", _
"ITEM NUMBER", "ITEM NAME", "COMPONENT", "QUANTITY", "SUBSCRIPTIONS", _
"QUANTITY", "QUANTITY", "NUMBER", "ITEM NAME", _
"PART NUMBER", "PART NAME", "EDITION", "TYPE", "VERSION", "USAGE", _
"LIMIT", "NAME", "TART DATE", "END DATE", "ASSET STATUS", _
"CATEGORY", "ACCOUNT TYPE", "METHOD", "CENTER")

Sheets("MPSA").Range("a14:ac14").Font.Name = "Calibri"
Sheets("MPSA").Range("a14:ac14").Interior.ColorIndex = 24
Sheets("MPSA").Range("a14:ac14").Font.Bold = True
Sheets("MPSA").Range("a14:ac14").Borders.LineStyle = xlContinuous
Sheets("MPSA").Columns.AutoFit


Dim Target_Path: Target_Path = Application.GetOpenFilename

Do While Target_Path <> False ' <-- loop until user cancels
    Dim Target_Workbook As Workbook: Set Target_Workbook = Workbooks.Open(Target_Path)

    Target_Workbook.Sheets(1).Cells.WrapText = True
    Target_Workbook.Sheets(1).Cells.WrapText = False

    Target_Workbook.Sheets(1).Range("A1").CurrentRegion.Offset(1).Copy _
        ThisWorkbook.Sheets("MPSA").Range("a1000000").End(xlUp).Offset(1)
    Target_Workbook.Close False

    ActiveWorkbook.Save
Dim e As VbMsgBoxResult: e = MsgBox("Another Record?", vbYesNo + vbQuestion, "Next details!")
    If e = vbNo Then
    ThisWorkbook.Save
    GoTo savefile
    Exit Sub
    End If
    'If e = vbYes Then

 Target_Path = Application.GetOpenFilename    
 Loop    
 GoTo savefile

 savefile:
 Application.DisplayAlerts = False    
 xlsname = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)ActiveWorkbook.SaveAs Filename:="C:\Users\" & Environ$("username") & "\Desktop\New Folder\" & xlsname & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

 Cleanup:
If Err.Number <> 0 Then MsgBox "Something went wrong: " & vbCrLf & Err.Description
Application.DisplayAlerts = True: Application.EnableEvents = True: Application.ScreenUpdating = True
End Sub

You should always qualify sheet references with a workbook object - don't rely on the ActiveWorkbook being what you think it ought to be. https://stackoverflow.com/questions/28439376/what-is-the-default-scope-of-worksheets-and-cells-and-range/28439984#28439984 If that's not the issue then you need to be more specific about exactly where the error occurs. — Tim Williams, Feb 07 '17 at 23:09
Do all the other workbooks have sheet with the correct name MPSA ? — Shmukko, Feb 07 '17 at 23:11
@TimWilliams I tried that, I don't know the issue, normally code works fine. — Ashwendra, Feb 07 '17 at 23:23
OK. have you tried debugging it? where does the error occur? — Shmukko, Feb 07 '17 at 23:28
I see unqualified worksheet references in your posted code - can you update to the version you tried with fully-qualified references? And again **where is the error occuring** ? — Tim Williams, Feb 07 '17 at 23:38
FWIW (nothing to do with your problem) an `Exit Sub` (or anything else) immediately after a `GoTo` statement will not do anything - the `GoTo` has moved the code execution pointer somewhere else. — YowE3K, Feb 08 '17 at 01:27
@YowE3K I tried removing `GoTo` and pasted `Saveas` line, The problem is same. — Ashwendra, Feb 08 '17 at 06:44
I believe this line `ThisWorkbook` in has a problem, I need to use this code with all the new different sheets everytime. `Target_Workbook.Sheets(1).Range("A1").CurrentRegion.Offset(1).Copy ThisWorkbook.Sheets("MPSA").Range("a1000000").End(xlUp).Offset(1)` `Target_Workbook.Close False` — Ashwendra, Feb 08 '17 at 07:01
Comment out the line `On Error GoTo Cleanup` - which line is highlighted when your code errors? — Tim Williams, Feb 08 '17 at 07:04
The comment I made has nothing to do with your problem - I was just pointing out that the `Exit Sub` statements directly after a `GoTo` statement will never get executed - they can be deleted. — YowE3K, Feb 08 '17 at 08:28
@TimWilliams I cannot see it because the code gives error only as an ADD-IN. — Ashwendra, Feb 08 '17 at 12:14
You can still debug an addin the same way you would for a regular workbook. The code is still viewable in the VBE — Tim Williams, Feb 08 '17 at 15:53
Hi @TimWilliams, I followed your instructions and got to know that there is a problem with `Target_Workbook.Sheets(1).Range("A1").CurrentRegion.Offset(1).Copy ThisWorkbook.Sheets("MPSA").Range("a1000000").End(xlUp).Offset(1)` — Ashwendra, Feb 08 '17 at 20:41
Yes, a code will first adds a `sheet("MPSA")` and then will call this above code. — Ashwendra, Feb 08 '17 at 21:20
You're adding a sheet "MPSA" to your add-in, or to some other workbook? ThisWorkbook is the file which contains the code which is running, so in your case that's your add-in. — Tim Williams, Feb 10 '17 at 16:08
@TimWilliams Oh man, I was wondering if that could be the problem, I need to add `MPSA` to the workbook for which I'll run add-in. Now, should I use `Activeworkbook` in place of `Thisworkbook` ? — Ashwendra, Feb 12 '17 at 13:37

score 0 · Accepted Answer · answered Feb 12 '17 at 15:06

0

The problem is solved now. As suggested by @Tom, Add-In was confused about which worksheet to paste values in.

Well I defined another variable using Dim Source_Workbook as Workbook Set Source_Workbook as ActiveWorkbook

Thanks to all of you :)

answered Feb 12 '17 at 15:06

Ashwendra

23
5

Code not working as `Add-In or Personal XLSB` [The same code works fine in workbook where it was created]

1 Answers1