title: How to assign values from one sheet into hidden sheet using Excel VBA? (and skip a column within the range?)
body: <p>Instead of copy-select-pasting cells (lower code), I wish to assign ranges directly and hide the sheet to which values are being filled.</p>
  
  <p>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?</p>
  
  <p>Trying to assign values on another sheet, I intend to build on this working code (with <a href="https://stackoverflow.com/questions/27726226/insert-values-from-one-sheet-into-anther">thanks to Jason Faulkner and aoswald</a>). (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.)</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Here is working code that I am trying to replace and simplify as indicated above. Are there additional simplifications that can be made?</p>
  
  <pre><code>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") &lt;&gt; 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
  </code></pre>
  
  <p>Edit: Here is the working code I've developed to replace the above code. What further improvements and simplifications can be made?</p>
  
  <pre><code>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." &amp; vbNewLine &amp; vbNewLine _
     &amp; "Note: The string you type will be exposed, i.e. not '***'." &amp; vbNewLine _
     &amp; "Note: This will save the Excel file!", "Enter Password: Enter the correct string.") &lt;&gt; 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
  </code></pre>