如何使用Excel VBA将一张纸中的值分配给隐藏的纸中? (并跳过范围内的列?) [英] How to assign values from one sheet into hidden sheet using Excel VBA? (and skip a column within the range?)

问题描述

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

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.)

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

这是我为替代上述代码而开发的工作代码.可以做哪些进一步的改进和简化?

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://stackoverflow.com/questions/11746478/excel-macro-run-time-error-1004
  Hidden_sheet.Unprotect MyPassword

 'References:
 ' dynamic referencing: https://stackoverflow.com/questions/45889866/how-to-assign-values-from-one-sheet-into-hidden-sheet-using-excel-vba-and-skip/45889960#45889960
 ' adding text:         https://stackoverflow.com/questions/20612415/adding-text-to-a-cell-in-excel-using-vba
 ' Union to exclude column: https://stackoverflow.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

推荐答案

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

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

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

因此，即使外部Range()的作用域为Private_sheet，也不会传递"内部的Range调用.

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

应该是这样的:

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

更简单/更灵活:

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

在工作表代码模块中，范围引用将默认为该工作表，但是仍然可以使用工作表对象(例如)Me.Range()

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()

这篇关于如何使用Excel VBA将一张纸中的值分配给隐藏的纸中? (并跳过范围内的列?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！