Excel宏仅将数据验证从[模板]工作表复制到其他几个工作表 [英] Excel macro to copy data validation only from a [template] worksheet to several other worksheets

问题描述

我正在使用Excel，并希望根据单元格地址获取表的名称（例如A3），该单元格将不会移动。我如何在Excel的VBA中说明这一点？

我的计划是将代码从维护选项卡上的一个表的一行复制到单个我的工作簿的每个选项卡上的表（减去我的TOC和数据选项卡）。每个标签都是TEMPLATE工作表（减去TOC，数据和TEMPLATE（Maint。）工作表）的副本。工作表数据，模板和模板（维护）可能隐藏或可能不会隐藏。

编辑：我想去做这个：

1. 在TEMPLATE（Maint。）工作表上复制数据验证


2. 检查C3目标工作表，找到什么表是分开的;单元格C3是能力中最左边的单元格，它是一个标题


3. 将数据验证复制到步骤2中找到的表中的每个数据行。


4. 重复步骤2和3，直到所有工作表都已将验证复制到（减去TOC，数据和TEMPLATE（Maint。）工作表）。

我的Copy_Data_Validations子文件中的代码如下：

 code> Dim TotalSheets As Integer 
 Dim p As Integer 
 Dim iAnswer As VbMsgBoxResult 
 
应用程序
 .DisplayAlerts = False 
 .ScreenUpdating =假
结束
 
'
'移动表TOC到工作簿的开头。 
'
表格（TOC）。之前：=表格（1）
'
'移动表数据作为工作簿中的第二张表。 
'
 Sheets（data）。Move Before：= Sheets（2）
 
 iAnswer = MsgBox（你即将复制数据验证！，vbOKCancel + vbExclamation _ 
 + vbDefaultButton2 + vbMsgBoxSetForeground，复制数据Valadations）
对于TotalSheets = 1 To Sheets.Count 
对于p = 3 To Sheets.Count  -  2 
'
'如果答案为是，则将数据验证从TEMPLATE（Maint。）复制到所有其他。
'表减去TOC表和数据表。
'
如果iAnswer = vbYes然后
如果UCase $（Sheets（p）.Name） TOC和UCase $（Sheets（p）.Name）"data然后
 
'这段代码应该只将Table1_1（A4：AO4）的数据验证
'从维护选项卡复制到每个工作表上的单个表的所有
'行（减去
'TOC，数据和TEMPLATE（Maint。）工作表。
'这是代码我正在寻找，除非
'有人有更好的东西可以想出来。 
 
 Selection.PasteSpecial粘贴：= xlPasteValidation，_ 
操作：= xlNone，SkipBlanks：= False，转置：= False 
如果
'
 '如果答案是取消，那么取消。 
'
 ElseIf iAnswer = vbCancel Then 
'在这里添加一个出口。 
如果
 
与应用程序
 .DisplayAlerts = True 
 .ScreenUpdating = True 
结束
  / pre> 
 
解决方案
 
  Sub test（）
 
 Dim ws作为工作表
 
与应用程序
 .DisplayAlerts = False 
 .ScreenUpdating = False 
结束
 
对于每个ws在ThisWorkbook.Worksheets 
如果ws.Name<> TOI和ws.Name<> DATA和ws.Name<> TEMPLATE（Maint。）然后
 Sheets（TEMPLATE（Maint。））。选择
 Range（Table1）。选择
 Selection.Copy 
 ws.Select 
 Range（A1）。选择
 Selection.PasteSpecial粘贴：= xlPasteValidation，操作：= xlNone，_ 
 SkipBlanks：= False，Transpose：= False 
 End If 
下一步
应用程序
 .DisplayAlerts = True 
 .ScreenUpdating = True 
结束
 
 End Sub 
  
 
I am using Excel and am looking to get the name of the table based on a cell address (ex A3), this cell will not move. How would I go about stating this in Excel's VBA?

My plan is to have code that will copy data validations from a row of one table on my Maintenance tab to a single table on each tab of my workbook (minus my "TOC" and "data" tabs). Each tab is a copy of a "TEMPLATE" worksheet (minus the "TOC", "data", & the "TEMPLATE (Maint.)" worksheets). Worksheets "data", "TEMPLATE", and "TEMPLATE (Maint.)" may or may not be hidden.

Edit: I would like to go about doing this by:

Copy data validations on the "TEMPLATE (Maint.)" worksheet
Check C3 on target worksheet and find what table it is apart of; cell C3 being the upper left most cell in the able, it is a header
Copy the data validations to each of the data rows of the table found in step 2.
Repeat steps 2 and 3 until all worksheets have had the validations copied to (minus the "TOC", "data", & the "TEMPLATE (Maint.)" worksheets).
The code I have in my "Copy_Data_Validations" sub is as follows:
Dim TotalSheets As Integer
Dim p As Integer
Dim iAnswer As VbMsgBoxResult

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

'
' Move sheet "TOC" to the begining of the workbook.
'
Sheets("TOC").Move Before:=Sheets(1)
'
' Move sheet "data" to be the second sheet in the workbook.
'
Sheets("data").Move Before:=Sheets(2)

iAnswer = MsgBox("You are about to copy data validations!", vbOKCancel + vbExclamation _
+ vbDefaultButton2 + vbMsgBoxSetForeground, "Copying Data Valadations")
For TotalSheets = 1 To Sheets.Count
    For p = 3 To Sheets.Count - 2
'
' If the answer is Yes, then copy data validations from "TEMPLATE (Maint.) to all other.
' sheets minus the "TOC" sheet and the "data" sheet.
'
        If iAnswer = vbYes Then
            If UCase$(Sheets(p).Name) <> "TOC" And UCase$(Sheets(p).Name) <> "data" Then

                ' This chunk of code should copy only the data validations
                ' of "Table1_1" (A4:AO4) from the maintenance tab to all
                ' rows of a single table on each worksheet (minus the
                ' "TOC", "data", & the "TEMPLATE (Maint.)" worksheets.
                ' This is the section of code I am looking for unless
                ' someone has something better they can come up with.

                Selection.PasteSpecial Paste:=xlPasteValidation, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            End If
'
' If the answer is Cancel, then cancels.
'
        ElseIf iAnswer = vbCancel Then
        ' Add an exit here.
        End If

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

解决方案
Sub test()

Dim ws As Worksheet

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

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "TOI" And ws.Name <> "DATA" And ws.Name <> "TEMPLATE (Maint.)" Then
        Sheets("TEMPLATE (Maint.)").Select
        Range("Table1").Select
        Selection.Copy
        ws.Select
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    End If
Next
With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
End With

End Sub


                        
这篇关于Excel宏仅将数据验证从[模板]工作表复制到其他几个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！