By Pass Message Box
Posted by Stanley on November 16, 2001 12:11 PM
The following macro to pull two columns of data/formulas from one worksheet and insert into another works fine except that it creates a message box saying: "A formula you want to move or copy contains the name 'AuditList', which already exists on the destination sheet. Do you want to use this version of the name? (more text but I think this describes it).
I'm sure the answer will always be "Yes". Is there VBA code I can add to answer yes and/or bypass this message?
Thanks, Stanley
ActiveSheet.Unprotect
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Sheets("Ranges").Select
Columns("K:L").Select
Selection.Copy
Sheets("Spreadsheet (2)").Select
Columns(ActiveCell.Column).Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
Range("b2").Select
Sheets("Ranges").Select
Application.CutCopyMode = False
Range("D1").Select
Sheets("Spreadsheet (2)").Select
ActiveSheet.Protect
Posted by Barrie Davidson on November 16, 2001 2:14 PM
Change your code to:
Application.DisplayAlerts = False
ActiveSheet.Unprotect
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Sheets("Ranges").Select
Columns("K:L").Select
Selection.Copy
Sheets("Spreadsheet (2)").Select
Columns(ActiveCell.Column).Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
Range("b2").Select
Sheets("Ranges").Select
Application.CutCopyMode = False
Range("D1").Select
Sheets("Spreadsheet (2)").Select
ActiveSheet.Protect
Application.DisplayAlerts = True
Regards,
BarrieBarrie Davidson
Posted by Stanley on November 16, 2001 2:48 PM
Barrie: Problem Persists
Barrie:
Pasted your code and still get the same message. In case additional info is useful, AuditList is a named range on the "Ranges" worksheet from which the 2 columns are being copied, and there are several data validation cells in one of the columns being copied and in similar columns already in the target worksheet ("Spreadsheet (2)"). When I run the macro, I have to check "Yes" twice (same message) to get the macro to finish running.
Let me know if there's another twist, if not, thanks for trying!
Cheers,
Stanley
