Consulting

Results 1 to 5 of 5

Thread: VBA Code error from Macro Add-In

  1. #1
    VBAX Regular
    Joined
    Jun 2017
    Location
    Windsor Mill
    Posts
    25
    Location

    VBA Code error from Macro Add-In

    I created a Macro and tried to add it to the Excel Ribbon as a new tab and the Macro will not run properly. It keeps getting hung up at "Sheet.Copy After:=ActiveWorkbook.Sheets(1)". The Macro is to open up a bunch of files into one workbook. Below is the complete VBA code. The Macro works fine if I run it normally but not as an Add-In from the Excel Ribbon.

    Sub GetFiles()
    
    
    Path = "G:\FSG\Howard\Monthly Bank Volume Reports\Bank Report Variance File\Banks\"
    Filename = Dir(Path & "*.xls")
      Do While Filename <> ""
      Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
         For Each Sheet In ActiveWorkbook.Sheets
         Sheet.Copy After:=ActiveWorkbook.Sheets(1)
      Next Sheet
         Workbooks(Filename).Close
         Filename = Dir()
      Loop
    
    
    Path = "G:\FSG\Howard\Monthly Bank Volume Reports\Bank Report Variance File\MACs\"
    Filename = Dir(Path & "*.xls")
      Do While Filename <> ""
      Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
         For Each Sheet In ActiveWorkbook.Sheets
         Sheet.Copy After:=ActiveWorkbook.Sheets(1)
      Next Sheet
         Workbooks(Filename).Close
         Filename = Dir()
      Loop
      
      ChDir "G:\FSG\Howard\Monthly Bank Volume Reports\Bank Report Variance File"
        ActiveWorkbook.SaveAs Filename:= _
            "G:\FSG\Howard\Monthly Bank Volume Reports\Bank Report Variance File\Volume Summary Report.xlsm", FileFormat:= _
            xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
      End Sub
    Last edited by Bob Phillips; 06-13-2017 at 03:40 PM. Reason: Added code tags

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    It doesn't make it an addin just because you run it from the ribbon, you have to save it as an xlam file to be an addin.

    If you have done that, have you activaed any workbook, the addin itself will be ThisWorkbook, not the active workbook.
    Last edited by Bob Phillips; 06-14-2017 at 01:58 PM.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Sub GetFiles() 
      c00 = "G:\FSG\Howard\Monthly Bank Volume Reports\Bank Report Variance File\Banks\" 
      c01 = Dir(c00 & "*.xls") 
      c02 =activeworkbook.name
    
      Do While c01 <> "" 
        with getobject(c00 & c01)
          For Each it In .Sheets 
            it.Copy , Workbooks(c02).Sheets(workbooks(c02).sheets.count) 
          Next 
          .Close 0 
        end with
        c01 = Dir 
      Loop
    end sub

  4. #4
    VBAX Regular
    Joined
    Jun 2017
    Location
    Windsor Mill
    Posts
    25
    Location
    Yes, I did that before I added it to the ribbon. I tried "ThisWorkbook" as well and it did not work. I just can't understand how a perfectly operating macro keeps getting hung up when I try to run it through the ribbon.

  5. #5
    VBAX Regular
    Joined
    Jun 2017
    Location
    Windsor Mill
    Posts
    25
    Location
    This code worked!! Thank you VBAX Guru. I just added to it so that it opens to sets of files and then save as.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •