1

With a click of a button, I'm trying to grab a range of data, paste the values, delete the original range of data then move the values back to the original location. Something like this...

ws.Range("A11:AD400").Copy
ws.Range("A511:AD900").PasteSpecial xlPasteValues
ws.Range("A11:AD400").Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.ClearContents
ws.Range("A511:AD900").Copy
ws.Range("A11:AD400").PasteSpecial xlPasteValues
ws.Range("A511:AD900").Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.ClearContents
Cœur
  • 37,241
  • 25
  • 195
  • 267
Bofett
  • 29
  • 5
  • why is the better question? And what isn't working with your code? – QHarr Mar 22 '18 at 15:51
  • @J_P I'm really just looking for a clean way to perform the task. In my opinion, this method is slow and clunky. – Bofett Mar 22 '18 at 15:53
  • I don't understand what you are trying to achieve with copying a range, deleting it, copying back its values to the same place? What are you achieving with this that can't be done in place? – QHarr Mar 22 '18 at 15:54
  • @QHarr I currently have users that paste into a sheet and it messes up the formatting. My thought was to have a repair button that could unprotect the sheet, copy all and paste values back into the range, then protect the sheet again. – Bofett Mar 22 '18 at 15:54
  • Why not just have code that applied the correct formatting for the repair? – QHarr Mar 22 '18 at 15:57
  • I didn't think of it that way, but love the idea. Can you please point me in the right direction for that? – Bofett Mar 22 '18 at 15:58

1 Answers1

1

If in effect you want to just restore correct formatting, record a macro as your format the target range ( ws.Range("A11:AD400")). Then that code can be re-used as your repair code which you can run to re-format the range as you require in future (you can link to a button for example).

Much more efficient than copying and pasting and copying back when it is only the formatting that is important.

Learning to use the macro-recorder:

  1. http://www.excel-easy.com/vba/examples/macro-recorder.html
  2. http://www.contextures.com/excel-macro-record-test.html

Intro to range formatting:

  1. http://www.excelhowto.com/macros/formatting-a-range-of-cells-in-excel-vba/
QHarr
  • 83,427
  • 12
  • 54
  • 101
  • This is great! thanks @ QHarr. Is there a way to restore conditional formatting this way as well? – Bofett Mar 22 '18 at 17:53
  • www.contextures.com/xlCondFormat04.html , https://stackoverflow.com/questions/13661965/conditional-formatting-using-excel-vba-code?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – QHarr Mar 22 '18 at 18:30
  • Did it make sense? – QHarr Mar 22 '18 at 20:40
  • 1
    Yes, thanks for your help, I ended up using this to reset the formats from a know good range. ws.Range("A398:AD398").Copy ws.Range("A11:AD399").PasteSpecial xlPasteFormats – Bofett Mar 23 '18 at 00:06