-
Dec 21st, 2012, 09:33 AM
#1
Thread Starter
Junior Member
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
-
Dec 21st, 2012, 03:35 PM
#2
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
-
Jan 3rd, 2013, 09:43 AM
#3
Thread Starter
Junior Member
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?
-
Jan 4th, 2013, 03:31 AM
#4
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|