Results 1 to 4 of 4

Thread: My Macro works on my computer but not others.

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Dec 2012
    Posts
    24

    My Macro works on my computer but not others.

    Hopefully you can help me out.. I have a macro that opens up a protected workbook and copies one sheet to the "master workbook". This works great on my computer but not my bosses computer or another fellow coworkers. My boss has the same type pf computer, everything is exactly the same versions.
    I found a post which told me that they think it might be settings. I checked in the Module screen Tools, References and made sure that they matched. I also checked in Excel the Security Trusted Sources. Other than that I think it might be code related. I have been having a lot of problems with the code Application.DisplayAlerts = False and True. For some reason it debugs on the code that I write between them. Is there another way to write that code without using Application.DisplayAlerts? Maybe that's not even the issue. I have no idea!!!! Below is my actual code.

    Code:
    Sub Copy_KIs()
    
    
    ' This section will delete the existing Tabs in the worksheet.
    
    Dim ws As Worksheet
    Application.DisplayAlerts = False
    For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Data Sheet" And ws.Name <> "Start Tab" Then ws.Delete
    'If Len(ws.CodeName) > 7 Then ws.Delete
    Next
    Application.DisplayAlerts = True
    
    
    
    'This section will add tabs to the worksheet for every client listed in Column A in the Start Tab.
    
    Sheets("Start Tab").Select
    Range("A2").Select
    Range(ActiveCell, ActiveCell.End(xlDown)).Select
    sourcesheet = ActiveSheet.Name
    For Each Cell In Selection
        Sheets.Add after:=Sheets(Sheets.Count) ' add tab at the very end
        ActiveSheet.Name = Cell.Value
        Range("A1").Select
        
        ActiveCell.FormulaR1C1 = _
            "=MID(CELL(""filename"",RC),FIND(""]"",CELL(""filename"",RC))+1,256)"
    Next Cell
    
    Sheets("Start Tab").Select
    
    
    'This will copy the client list to the Data Sheet.
    Sheets("Data Sheet").Select
        Range("D10").Select
        ActiveCell = "1"
    
    Sheets("Data Sheet").Select
    Range("A:A").ClearContents
    
    Sheets("Start Tab").Select
    Range("A2").Select
    Range(ActiveCell, ActiveCell.End(xlDown)).Select
    Selection.Copy
    Sheets("Data Sheet").Select
    Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    ActiveCell.Copy
    Range("F2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    
        
        
    'This will open the Key Indicators and copy them to the spreadsheet.
    
    
    
    Do
        
        Application.DisplayAlerts = False
        Workbooks.Open Filename:=Cells(12, 4), Password:="123", UpdateLinks:=False
        Application.DisplayAlerts = True
        Sheets("Summary").Select
        ActiveWindow.FreezePanes = False
        Cells.Select
        Selection.Copy
        Windows("Key Indicators Summary.xlsm").Activate
        Sheets("Data Sheet").Select
        Sheets(Range("D14").Value).Select
        Cells.Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        ActiveWindow.Zoom = 75
        DoEvents
        
        Range("A1").Select
        Sheets("Data Sheet").Select
        Application.DisplayAlerts = False
        Workbooks(Range("D14").Value).Close
        Application.DisplayAlerts = True
        DoEvents
        
        Sheets("Data Sheet").Select
        NumFiles = ActiveSheet.Range("NumFiles")
            MultiKounter = MultiKounter + 1
            Range("MultiKounter").Select
           MultiKounter = ActiveSheet.Range("MultiKounter")
            ActiveCell.FormulaR1C1 = MultiKounter + 1
        
      Loop While MultiKounter <= NumFiles
      
      On Error GoTo Errhandler:
    Errhandler:
      Sheets("Start Tab").Select
    
      
      Dim w As Workbook, ss As Worksheet
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "Data Sheet" And ws.Name <> "Start Tab" Then
                If w Is Nothing Then
                    ws.Copy
                    Set w = ActiveWorkbook
                Else
                    ws.Copy after:=ss
                End If
                Set ss = ActiveSheet
                Rows("80:112").Select
        Selection.Delete Shift:=xlUp
        Range("A1").Select
        
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
           
        
        Worksheets(1).Activate
      
    End With
     
    End If
    
      Next ws
      
    
    End Sub
    Last edited by Siddharth Rout; Jan 4th, 2013 at 04:00 AM. Reason: Added Code tags

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: My Macro works on my computer but not others.

    For some reason it debugs on the code that I write between them.
    maybe there is an error but the alert is suppressed
    temporarily comment out the displayalerts = false and test
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Dec 2012
    Posts
    24

    Re: My Macro works on my computer but not others.

    Hi westconn1. I commented out all of the display alerts and it works (Thanks!!) but all the pop ups come up now. Is there another way to stop them from coming up?

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: My Macro works on my computer but not others.

    Is there another way to stop them from coming up?
    some but not all

    Code:
    Workbooks(Range("D14").Value).Close False
    or true to save

    you need to figure out which alert is breaking your code, so you can put back to hide the alerts till you find which can not be ignored
    make a list of all the code lines that fire an alert, then post here
    the example i posted above will eliminate some alerts, but i doubt that that is the one causing your problem

    avoid selecting and activating, as these can cause problems, also avoid use of active anything or selection as far as possible, there is little that can not be done by alternative methods (code)
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width