CODEWITHSUNDEEP
vbaexcel
David S.
David S.

Reputation: 119

Data validations lost when copying worksheet using VBA macro

Problem: I am having a problem with data validations not copying to the copied worksheet when a worksheet is copied using a macro. Is there any way to do this using my current code?

Yes, I am also aware that there is a similar question (Here: Data validation lost when I copy a worksheet to another workbook) but it isn't quite the same issue and does not yet at this time have an answer. Any help to get these data validations to copy along with the data would be much appreciated and will save hours of needless repetitive work.

Edit: This code is in the "ThisWorkbook" section of my workbook.

My code is as follows:

Dim wb As Workbook
Dim wsTemp As Worksheet
Dim sName As String
Dim bValidName As Boolean
Dim i As Long

bValidName = False

Do While bValidName = False
    sName = InputBox("Please name this new worksheet:", "New Sheet Name", Sh.Name)
        If Len(sName) > 0 Then
        For i = 1 To 7
            sName = Replace(sName, Mid(":\/?*[]", i, 1), " ")
        Next i
        sName = Trim(Left(WorksheetFunction.Trim(sName), 31))
        If Not Evaluate("ISREF('" & sName & "'!A1)") Then bValidName = True
    End If
Loop

With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    .EnableEvents = False
End With

Set wb = ThisWorkbook
Set wsTemp = wb.Sheets("TEMPLATE")

wsTemp.Visible = xlSheetVisible
wsTemp.Copy After:=wb.Sheets(wb.Sheets.Count)
ActiveSheet.Name = sName
Sh.Delete
wsTemp.Visible = xlSheetHidden   'Or xlSheetVeryHidden

With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
    .EnableEvents = True
End With

' Call Sort_Active_book
' Call Rebuild_TOC

Upvotes: 1

Views: 1798

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

You should be able to copy a worksheet and retain DV. This example:

  • Activates Sheet1
  • creates a simple DV on Sheeet1
  • copies Sheet1 to the end of the workbook


Sub Macro2()
    Sheets("Sheet1").Select
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "alpha"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "beta"
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "gamma"
    Range("B1").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$D$1:$D$3"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Sheets("Sheet1").Select
    Sheets("Sheet1").Copy After:=Sheets(3)
End Sub

This is recorded code run on a new, empty workbook on a Win 7/Excel 2007 system.

Can you replicate my result ??

If my code works on your system, begin by trying to mimic your VBA code manually with the recorder turned. Then take your recorded code and modify it to include non-recordable parts, (like the InputBox statements).

Upvotes: 1

Related Questions