title: Copy only visible cells and paste onto only visible cells dynamic macro
body: <p>What I am trying to accomplish is a dynamic macro that can be used in many different workbooks to achieve the following:  I would like to have the user input a range that they would like to copy.  This range will be filtered.  Then I would like to have the user select range to paste the copied data.  The range they will be pasting into is also filtered (may be different filters than where the data was copied from.  IDEALLY the user would only select the top left cell of the range to paste onto (instead of having to select the entire thing).</p>
  
  <p>The code below will copy the filtered data (visible cells only) as I would like.</p>
  
  <pre><code>Dim RangeCopy As Range
  Dim RangeDest As Range
  
  Set RangeCopy = Application.InputBox("Select a range to copy ", "Obtain     Range Object", Type:=8)
      MsgBox "The range you selected to copy is " &amp; RangeCopy.Address
          RangeCopy.Select
  
  Selection.SpecialCells(xlCellTypeVisible).Select 'selects visible cells only from previously selected range
      Selection.Copy
  </code></pre>
  
  <p>Pasting is of course the tricky part.  I have found that I can manually "paste" successfully in the following manner:</p>
  
  <p>Assume that the copied range is A1:A10 and the paste range is B10:B20</p>
  
  <p>I can input the formula "= A1" into cell B10 ---> copy cell B10 ----> select the desired range to paste onto ----> use "Alt ;" shortcut ----> paste. </p>
  
  <p>The following code attempts to automate this logic in VBA:</p>
  
  <pre><code>Dim RangeCopy As Range
  Dim RangeDest As Range
  
  Set RangeCopy = Application.InputBox("Select top cell of range to copy ", "Obtain Range Object", Type:=8)
      MsgBox "The top cell of the range you would like to copy is " &amp; RangeCopy.Address
  
  
  Set RangeDest = Application.InputBox("Select the top of the range to paste onto ", "Obtain Range Object", Type:=8)
      MsgBox "The top of the range you have selected to paste onto is " &amp; RangeDest.Address
  
  RangeDest.Formula = "=RangeCopy"
  RangeDest.Copy
  
  Range(Selection, Selection.End(xlDown)).Select
  Selection.SpecialCells(xlCellTypeVisible).Select
      ActiveSheet.Paste
  
  Application.CutCopyMode = False
  Calculate
  </code></pre>
  
  <p>This presents two issues: </p>
  
  <ol>
  <li><p>It pastes correctly onto only visible cells but it is currently entering "=CopyRange" as text into the range I want to paste onto (instead of a formula setting the "paste cell" equal to the "copy cell".</p></li>
  <li><p>This code does not yet allow a user to select and exact range.  It allows them to select a starting point but then will copy and paste to the end of the column being pasted onto.  I need the user to be able to select a range and have yet to find a way to do so without errors occurring. </p></li>
  </ol>
  
  <p>Searching online I have found other versions of "pasting onto visible cells macros".  I tried to combine them with the first bit of code I shared in this post.  This combination is shown below.  </p>
  
  <pre><code>Sub Copy_Paste_Visible_Cells()
  
  Dim RangeCopy As Range
  Dim RangeDest As Range
  
  Set RangeCopy = Application.InputBox("Select a range to copy ", "Obtain     Range Object", Type:=8)
      MsgBox "The range you selected to copy is " &amp; RangeCopy.Address
          RangeCopy.Select
  
  Set RangeDest = Application.InputBox("Select range to paste onto ", "Obtain Range Object", Type:=8)
      MsgBox "The range you have slected to paste onto is " &amp; RangeDest.Address
  
  Selection.SpecialCells(xlCellTypeVisible).Select 'selects visible cells only from previously selected range
  Selection.Copy
  
  Dim rng1 As Range
  Dim rng2 As Range
  
      For Each rng2 In RangeDest
          If rng2.EntireRow.RowHeight &gt; 0 Then
              rng2.PasteSpecial
              Set RangeDest = rng2.Offset(1).Resize(RangeDest.Rows.Count)
              Exit For
          End If
      Next
  
  Application.CutCopyMode = False
  
  End Sub
  </code></pre>
  
  <p>This runs without error but the macro only pastes until it hits a hidden row.  So if rows 1,2 3 and 6 are visible but 4 and 5 are hidden, the macro will paste onto 1,2 and 3 but not 4,5, or 6.</p>
  
  <p>I have made several other attempts but these seem to be the most promising thus far.  Any suggestions / help anyone can offer are greatly appreciated.  The biggest key is to make this completely dynamic and as intuitive as possible for the user.  </p>
  
  <p>Thank you in advance !</p>