如何使用Excel VBA将值从一张表分配到隐藏表中？ （并跳过范围内的列？）

Question

而不是复制粘贴单元格（较低代码），我希望直接指定范围并隐藏要填充值的表单。

我认为工作表可以简单地从视图中隐藏起来，而基于另一个工作表范围填充值的宏仍然可以工作，对吗？

试图在另一张工作表上分配数值，我打算在此工作代码上生成（使用thanks to Jason Faulkner and aoswald）。 （我必须将单元格放在最后一组值的空白列之后，理想情况下，代码将从A13：C##（直到最后一个填充的行）和E13：E ##之后立即分配值（即删除列D 。分配当值设置到隐藏的工作表）

Private Sub CommandButton1_Click() 
Dim DataRange As Variant, Constraint_sheet As Worksheet, Private_sheet As Worksheet 
Set Constraint_sheet = Sheets("Constraint Sheet") 
Set Private_sheet = Sheets("Private") 
DataRange = Constraint_sheet.Range("A13:C300").Value 
With Private_sheet 
    .Range(.Range("XFD1").End(xlToLeft).Offset(0, 3), .Range("XFD1").End(xlToLeft).Offset(287, 2)) = DataRange 
End With 
End Sub 

这里工作，我试图取代，并简化如上所示的代码是可以进行有附加的简化

Private Sub CommandButton1_Click() 
    Dim MyPassword As String, Private_sheet As Worksheet 
    Set Private_sheet = Sheets("Private") 
    MyPassword = "string" 
    If InputBox("Please enter the password to continue.", "Enter Password") <> MyPassword Then 
    Exit Sub 
    End If 

    Private_sheet.Unprotect MyPassword ' apparently causes clipboard to be erased so do before copying cells 

    Columns("B:E").Select 
    Application.CutCopyMode = False 
    Selection.Copy 

    Private_sheet.Select 
Private_sheet.Range("XFD1").End(xlToLeft).Offset(0, 3).Select 
ActiveCell.PasteSpecial 
ActiveCell.CurrentRegion.EntireColumn.Locked = True 
ActiveCell.CurrentRegion.Offset(0, -1).EntireColumn.Locked = True 
Private_sheet.Protect MyPassword 

    ActiveWorkbook.Save 

End Sub 

编辑？以下是我开发的用于取代上述代码的工作代码。可以进行哪些进一步的改进和简化？

Private Sub AddTemplate_Click() 
Dim Exposed_sheet As Worksheet, Hidden_sheet As Worksheet, MyPassword As String 

Set Exposed_sheet = Sheets("Exposed Sheet") 
Set Hidden_sheet = Sheets("Hidden") 

    MyPassword = "string" 
    'Reference: carriage return in msgbox http://www.ozgrid.com/forum/showthread.php?t=41581 
    If InputBox("Please enter the password to continue." & vbNewLine & vbNewLine _ 
    & "Note: The string you type will be exposed, i.e. not '***'." & vbNewLine _ 
    & "Note: This will save the Excel file!", "Enter Password: Enter the correct string.") <> MyPassword Then 
    Exit Sub 
    End If 

' Reference: .Protect - https://*.com/questions/11746478/excel-macro-run-time-error-1004 
    Hidden_sheet.Unprotect MyPassword 

'References: 
' dynamic referencing: https://*.com/questions/45889866/how-to-assign-values-from-one-sheet-into-hidden-sheet-using-excel-vba-and-skip/45889960#45889960 
' adding text:   https://*.com/questions/20612415/adding-text-to-a-cell-in-excel-using-vba 
' Union to exclude column: https://*.com/questions/2376995/exclude-some-columns-while-copying-one-row-to-other 
With Hidden_sheet 
    .Cells(1, Columns.Count).End(xlToLeft).Offset(1, 3).Resize(UBound(Exposed_sheet.Range("B6", "D9").Value, 1), UBound(Exposed_sheet.Range("B6", "D9").Value, 2)).Value = Exposed_sheet.Range("B6", "D9").Value 
    .Cells(1, Columns.Count).End(xlToLeft).Offset(1, 6).Value = "Volume/Protocol" 
    .Cells(1, Columns.Count).End(xlToLeft).Offset(6, 3).Resize(UBound(Union(Exposed_sheet.Range("A13:C300"), Exposed_sheet.Range("E13:E300")).Value, 1), UBound(Union(Exposed_sheet.Range("A13:C300"), Exposed_sheet.Range("E13:E300")).Value, 2)).Value = Union(Exposed_sheet.Range("A13:C300"), Exposed_sheet.Range("E13:E300")).Value 
    ' If you change the order putting this prior, you must change the offsets or the cell they count from. -- DB, Aug 28 2017 
    .Cells(1, Columns.Count).End(xlToLeft).Offset(0, 3).Resize(1, 3).Merge 
    .Cells(1, Columns.Count).End(xlToLeft).Offset(0, 3).Value = Exposed_sheet.Range("A1").Value 
End With 

Hidden_sheet.Protect MyPassword 

ActiveWorkbook.Save 

End Sub 

Answer 1

你的问题是，（在普通的代码模块）Range()，Cells()总会引用ActiveSheet，除非你有一个工作表预选赛

Private_sheet.Range(Range("XFD1").End(xlToLeft).Offset(0, 3), _ 
        Range("XFD1").End(xlToLeft).Offset(287, 2)) = DataRange 

因此，即使外Range()的作用域Private_sheet，即不会“通过”到呼叫的内部Range。

应该是这样的：

With Private_sheet 
    .Range(.Range("XFD1").End(xlToLeft).Offset(0, 3), _ 
      .Range("XFD1").End(xlToLeft).Offset(287, 2)) = DataRange 
End With 

有点简单/更灵活：

​​

在表代码模块，范围引用将默认的是表，但它仍然有资格好的做法与表单对象（例如）Me.Range()