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

Instead of copy-select-pasting cells (lower code), I wish to assign ranges directly and hide the sheet to which values are being filled.

我希望直接分配范围并隐藏要填充值的工作表，而不是复制选择粘贴单元格（下面的代码）。

I think the sheet can simply be hidden from view and the macro to fill values based on another sheet's ranges will still work, right?

我认为可以简单地从视图中隐藏工作表，并且基于另一个工作表范围填充值的宏仍然有效，对吧？

Trying to assign values on another sheet, I intend to build on this working code (with thanks to Jason Faulkner and aoswald). (I must place the cells after one blank column from the last set of values. Ideally, the code will assign values from A13:C## (til the last filled row) and likewise E13:E## immediately after it (i.e. removing column D when assigning values onto the hidden sheet.)

试图在另一张纸上分配值，我打算在这个工作代码的基础上（感谢Jason Faulkner和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

Here is working code that I am trying to replace and simplify as indicated above. Are there additional simplifications that can be made?

这是我正在尝试替换和简化的工作代码，如上所示。是否可以进行其他简化？

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

Edit: Here is the working code I've developed to replace the above code. What further improvements and simplifications can be made?

编辑：这是我为替换上述代码而开发的工作代码。可以进一步改进和简化哪些方面？

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

1 个解决方案

#1

Your problem is that (in a regular code module) Range(), Cells() will always reference the ActiveSheet unless you include a worksheet qualifier

您的问题是（在常规代码模块中）Range（），除非包含工作表限定符，否则Cells（）将始终引用ActiveSheet

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

So even though the outer Range() is scoped to Private_sheet, that does not "carry through" to the inner Range calls.

因此，即使外部Range（）的范围限定为Private_sheet，也不会“遍历”内部Range调用。

Should be something like:

应该是这样的：

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

Bit simpler/more flexible:

更简单/更灵活：

'EDITED
Private_sheet.Cells(1, Columns.Count).End(xlToLeft).Offset(0, 3) _
  .Resize(UBound(DataRange, 1), UBound(DataRange, 2)).Value = DataRange

In a sheet code module, range references will default to that sheet, but it's still good practise to qualify with a sheet object (eg) Me.Range()

在工作表代码模块中，范围引用将默认为该工作表，但使用工作表对象（例如）Me.Range（）进行限定仍然是一种好习惯。