title: How can I copy non-contiguous cells from a workbook to a different set of non-contiguous cells in another workbook?
body: <p>I have a Master blank workbook that users copy, to record one year's worth of information.  The Master blank allows the user to point to last year's 'old' workbook, insert the proper number of rows into the new to match the old, and then copy/paste two different contiguous ranges from the old into matching ranges in the "new" blank workbook.</p>
  
  <p>Now, I want it to copy the values that are the totals from non-contiguous columns on the old worksheet into different non-contiguous cells on the new worksheet.</p>
  
  <p>The totals are on a different row for each user so I use a lastrow function to find the row number.  But it seems I cannot use that in defining the non-contiguous ranges.</p>
  
  <p>All the code is included below.  You will notice a section where I'm trying to copy all of the data from an old worksheet into the new worksheet using Union on ranges because it too is a bunch of non-contiguous cells, but it's not working either.  If I get the first problem solved, I should be able to adapt it to the second problem.</p>
  
  <p><strong>Edit:</strong><br> 
  I modified the "union" section and now all of the correct cells are being selected but <code>Selection.Copy</code> fails.  What's the alternative?</p>
  
  <p><strong>Edit #2:</strong><br>
  I added two screenshots of the Master blank and a user's file.  It is easy to see a) the number of rows is different and b) the shaded areas are the ones I wish to copy/paste (in the 'union' section of code).  In the next pair of screenshots, the red and green cells of the user's file need to be imported into the corresponding red and green cells of the Master blank file.</p>
  
  <pre><code>Option Explicit
  Sub UpdateFromOld()
  
  Dim fd As FileDialog
  Dim NewWbk As Workbook, OldWbk As Workbook
  Dim vrtSelectedItem As Variant, fname As Variant
  Dim cella As Range, cellb As Range, cell1 As Range, cell2 As Range
  Dim wsh As Worksheet, wsh2 As Worksheet
  Dim WshName As String, WshName2 As String
  Dim Answer1 As String, Answer2 As String
  Dim UsedRange1 As Range, UsedRange2 As Range
  Dim InputRange As Range, InputRange1 As Range, InputRange2 As Range, InputRange3 As Range, InputRange4 As Range, InputRange5 As Range
  Dim InputRange6 As Range, InputRange7 As Range, InputRange8 As Range, InputRange9 As Range, InputRange10 As Range, InputRange11 As Range
  Dim LstYr, ThisYr
  Dim ExtraRows As Integer, RowCounter As Integer
  Dim SumArray1(24)
  Dim MyCell1, cell
  
  On Error GoTo ErrorHandler
  
  Range("B5").Select
  WshName = InputBox("Type in your location name", "Annual Ad Planner")
  Range("B5").Value = WshName
  ActiveSheet.Name = WshName
  Set wsh = Worksheets(WshName)
  
  'Application.ScreenUpdating = False
  
  'select the old file to update from
  MsgBox "In the next window, navigate to and select the Ad Planner file you are updating from.", vbOKOnly, "Annual Ad Planner"
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  With fd
      .Filters.Add "Previous Ad Planner", "*.xls", 1
      If .Show = -1 Then
          For Each vrtSelectedItem In .SelectedItems
              fname = vrtSelectedItem
          Next vrtSelectedItem
      Else
          MsgBox "You ended the update process.", vbOKOnly, "Annual Ad Planner"
          GoTo ErrorHandler
      End If
  End With
  
  Set OldWbk = Workbooks.Open(fname)
  OldWbk.Unprotect
  Set NewWbk = ThisWorkbook
  NewWbk.Unprotect
  Set fd = Nothing
  
  
  NewWbk.Worksheets(WshName).Visible = True
  NewWbk.Worksheets(WshName).Activate
  NewWbk.Worksheets(WshName).Unprotect
  Set cella = Cells(Rows.Count, "A").End(xlUp).Offset(0, 0)
  
  OldWbk.Activate
  Range("B5").Select
  WshName2 = ActiveCell.Worksheet.Name
  Set wsh2 = Worksheets(WshName2)
  OldWbk.Worksheets(WshName2).Visible = True
  OldWbk.Worksheets(WshName2).Activate
  OldWbk.Worksheets(WshName2).Unprotect
  Set cellb = Cells(Rows.Count, "B").End(xlUp).Offset(0, 0)
  
  Range("B5").Select
  Selection.Copy
  NewWbk.Activate
  Range("B5").Select
  Range("B5").PasteSpecial xlPasteValues
  
  Range("B23").Select
  If cellb.Row &gt; cella Then
      ExtraRows = cellb.Row - cella
      For RowCounter = 1 To ExtraRows
          AddRow
      Next RowCounter
  End If
  NewWbk.Unprotect
  NewWbk.Worksheets(WshName).Unprotect
  
  'Copy &amp; Paste list of lead sources
  OldWbk.Activate
  Range("B20:B" &amp; cellb.Row - 1).Select
  Selection.Copy
  NewWbk.Activate
  Range("B20").Select
  Range("B20").PasteSpecial xlPasteValues
  
  'Copy &amp; Paste classifications &amp; segments
  OldWbk.Activate
  Range("CI20:CK" &amp; cellb.Row - 1).Select
  Selection.Copy
  NewWbk.Activate
  Range("CI20").Select
  Range("CI20").PasteSpecial xlPasteValues
  Application.CutCopyMode = False
  
  Answer1 = MsgBox("Are you importing last year's file?", vbYesNoCancel, "Annual Ad Planner")
  If Answer1 = vbNo Then
      Answer2 = MsgBox("Are you updating the 2014 file?", vbYesNoCancel, "Annual Ad Planner")
      If Answer2 = vbYes Then
          Set InputRange = Union(Range("C3"), Range("C4"), Range("C6"), Range("C7"), Range("C9"), Range("E6"), Range("E7"), Range("E9"), Range("J3"), Range("J4"), Range("J6"), Range("J7"), Range("J9"), Range("L6"), Range("L7"), Range("L9"), Range("Q3"), Range("Q4"), Range("Q6"), Range("Q7"), Range("Q9"), Range("S6"), Range("S7"), Range("S9"), Range("X3"), Range("X4"), Range("X6"), Range("X7"), Range("X9"), Range("Z6")) '30 ranges
          Set InputRange1 = Union(Range("Z7"), Range("Z9"), Range("AE3"), Range("AE4"), Range("AE6"), Range("AE7"), Range("AE9"), Range("AG6"), Range("AG7"), Range("AG9"), Range("AL3"), Range("AL4"), Range("AL6"), Range("AL7"), Range("AL9"), Range("AN6"), Range("AN7"), Range("AN9"), Range("AS3"), Range("AS4"), Range("AS6"), Range("AS7"), Range("AS9"), Range("AU6"), Range("AU7"), Range("AU9"), Range("AZ3"), Range("AZ4")) '28 ranges
          Set InputRange2 = Union(Range("AZ6"), Range("AZ7"), Range("AZ9"), Range("BB6"), Range("BB7"), Range("BB9"), Range("BG3"), Range("BG4"), Range("BG6"), Range("BG7"), Range("BG9"), Range("BI6"), Range("BI7"), Range("BI9"), Range("BN3"), Range("BN4"), Range("BN6"), Range("BN7"), Range("BN9"), Range("BP6"), Range("BP7"), Range("BP9"), Range("BU3"), Range("BU4"), Range("BU6"), Range("BU7"), Range("BU9"), Range("BW6")) '28 ranges
          Set InputRange3 = Union(Range("BW7"), Range("BW9"), Range("CB3"), Range("CB4"), Range("CB6"), Range("CB7"), Range("CB9"), Range("CD6")) '8 ranges
          Set InputRange4 = Union(Range("CD7"), Range("CD9"), Range("C20:D" &amp; cellb.Row - 1), Range("F20:G" &amp; cellb.Row - 1), Range("J20:K" &amp; cellb.Row - 1), Range("M20:N" &amp; cellb.Row - 1), Range("Q20:R" &amp; cellb.Row - 1), Range("T20:U" &amp; cellb.Row - 1), Range("X20:Y" &amp; cellb.Row - 1), Range("AA20:AB" &amp; cellb.Row - 1), Range("AE20:AF" &amp; cellb.Row - 1), Range("AH20:AI" &amp; cellb.Row - 1), Range("AL20:AM" &amp; cellb.Row - 1), Range("AO20:AP" &amp; cellb.Row - 1), Range("AS20:AT" &amp; cellb.Row - 1), Range("AV20:AW" &amp; cellb.Row - 1), Range("AZ20:BA" &amp; cellb.Row - 1), Range("BC20:BD" &amp; cellb.Row - 1), Range("BG20:BH" &amp; cellb.Row - 1), Range("BJ20:BK" &amp; cellb.Row - 1), Range("BN20:BO" &amp; cellb.Row - 1), Range("BQ20:BR" &amp; cellb.Row - 1), Range("BU20:BV" &amp; cellb.Row - 1), Range("BX20:BY" &amp; cellb.Row - 1), Range("CB20:CC" &amp; cellb.Row - 1), Range("CE20:CF" &amp; cellb.Row - 1)) ' 26 ranges with unknown # of cells
          Set InputRange5 = Union(InputRange, InputRange1, InputRange2, InputRange3, InputRange4)
          OldWbk.Activate
          Set InputRange6 = Union(Range("C3"), Range("C4"), Range("C6"), Range("C7"), Range("C9"), Range("E6"), Range("E7"), Range("E9"), Range("J3"), Range("J4"), Range("J6"), Range("J7"), Range("J9"), Range("L6"), Range("L7"), Range("L9"), Range("Q3"), Range("Q4"), Range("Q6"), Range("Q7"), Range("Q9"), Range("S6"), Range("S7"), Range("S9"), Range("X3"), Range("X4"), Range("X6"), Range("X7"), Range("X9"), Range("Z6")) '30 ranges
          Set InputRange7 = Union(Range("Z7"), Range("Z9"), Range("AE3"), Range("AE4"), Range("AE6"), Range("AE7"), Range("AE9"), Range("AG6"), Range("AG7"), Range("AG9"), Range("AL3"), Range("AL4"), Range("AL6"), Range("AL7"), Range("AL9"), Range("AN6"), Range("AN7"), Range("AN9"), Range("AS3"), Range("AS4"), Range("AS6"), Range("AS7"), Range("AS9"), Range("AU6"), Range("AU7"), Range("AU9"), Range("AZ3"), Range("AZ4")) '28 ranges
          Set InputRange8 = Union(Range("AZ6"), Range("AZ7"), Range("AZ9"), Range("BB6"), Range("BB7"), Range("BB9"), Range("BG3"), Range("BG4"), Range("BG6"), Range("BG7"), Range("BG9"), Range("BI6"), Range("BI7"), Range("BI9"), Range("BN3"), Range("BN4"), Range("BN6"), Range("BN7"), Range("BN9"), Range("BP6"), Range("BP7"), Range("BP9"), Range("BU3"), Range("BU4"), Range("BU6"), Range("BU7"), Range("BU9"), Range("BW6")) '28 ranges
          Set InputRange9 = Union(Range("BW7"), Range("BW9"), Range("CB3"), Range("CB4"), Range("CB6"), Range("CB7"), Range("CB9"), Range("CD6")) '8 ranges
          Set InputRange10 = Union(Range("CD7"), Range("CD9"), Range("C20:D" &amp; cellb.Row - 1), Range("F20:G" &amp; cellb.Row - 1), Range("J20:K" &amp; cellb.Row - 1), Range("M20:N" &amp; cellb.Row - 1), Range("Q20:R" &amp; cellb.Row - 1), Range("T20:U" &amp; cellb.Row - 1), Range("X20:Y" &amp; cellb.Row - 1), Range("AA20:AB" &amp; cellb.Row - 1), Range("AE20:AF" &amp; cellb.Row - 1), Range("AH20:AI" &amp; cellb.Row - 1), Range("AL20:AM" &amp; cellb.Row - 1), Range("AO20:AP" &amp; cellb.Row - 1), Range("AS20:AT" &amp; cellb.Row - 1), Range("AV20:AW" &amp; cellb.Row - 1), Range("AZ20:BA" &amp; cellb.Row - 1), Range("BC20:BD" &amp; cellb.Row - 1), Range("BG20:BH" &amp; cellb.Row - 1), Range("BJ20:BK" &amp; cellb.Row - 1), Range("BN20:BO" &amp; cellb.Row - 1), Range("BQ20:BR" &amp; cellb.Row - 1), Range("BU20:BV" &amp; cellb.Row - 1), Range("BX20:BY" &amp; cellb.Row - 1), Range("CB20:CC" &amp; cellb.Row - 1), Range("CE20:CF" &amp; cellb.Row - 1)) ' 26 ranges with unknown # of cells
          Set InputRange11 = Union(InputRange6, InputRange7, InputRange8, InputRange9, InputRange10)
          InputRange11.Select
          Selection.Copy
          NewWbk.Activate
          InputRange5.Select
          Selection.PasteSpecial xlPasteValues
      Else
      End If
  ElseIf Answer1 = vbYes Then
      Set LstYr = OldWbk.Worksheets(WshName2).Range("F" &amp; cellb.Row, "G" &amp; cellb.Row, "M" &amp; cellb.Row, "N" &amp; cellb.Row, "T" &amp; cellb.Row, "U" &amp; cellb.Row, "AA" &amp; cellb.Row, "AB" &amp; cellb.Row, "AH" &amp; cellb.Row, "AI" &amp; cellb.Row, "AO" &amp; cellb.Row, "AP" &amp; cellb.Row, "AV" &amp; cellb.Row, "AW" &amp; cellb.Row, "BC" &amp; cellb.Row, "BD" &amp; cellb.Row, "BJ" &amp; cellb.Row, "BK" &amp; cellb.Row, "BQ" &amp; cellb.Row, "BR" &amp; cellb.Row, "BX" &amp; cellb.Row, "BY" &amp; cellb.Row, "CE" &amp; cellb.Row, "CF" &amp; cellb.Row)  '24 ranges
      Set ThisYr = NewWbk.Worksheets(WshName).Range("C3, C4, J3, J4, Q3, Q4, X3, X4, AE3, AE4, AL3, AL4, AS3, AS4, AZ3, AZ4, BG3, BG4, BN3, BN4, BU3, BU4, CB3, CB4") '24 ranges
      OldWbk.Activate
      OldWbk.Worksheets(WshName2).Range("F" &amp; cellb.Row).Select
  
      For MyCell1 = 1 To 24
          SumArray1(MyCell1) = 0
      Next MyCell1
      MyCell1 = 1
  
      For Each cell In LstYr
          SumArray1(MyCell1) = cell.Value
          MyCell1 = MyCell1 = 1
      Next cell
  
      NewWbk.Activate
      MyCell1 = 1
      For Each cell In ThisYr
          cell.Value = SumArray1(MyCell1)
          MyCell1 = MyCell1 = 1
      Next cell
  End If
  OldWbk.Close SaveChanges:=False
  NewWbk.Protect
  
  Application.ScreenUpdating = True
  
  ErrorHandler:
      Resume Next
  
  End Sub
  </code></pre>
  
  <p>[screenshots hosted on flickr] <a href="http://www.flickr.com/photos/32470349@N03/11873809585/" rel="nofollow noreferrer">http://www.flickr.com/photos/32470349@N03/11873809585/</a></p>