Search code examples
excelvba

excel vba assign value to variable with all styles

The code is performing a copy from area Q145:AE211 on button click and pasting in a continues way to next cells after each button click. The problem is that area Q145:AE211 without data contains also all styles for cells - colors, borders, customised cell width and also merged cells formatting. Right now it copies only data. How to assign to "arr" everything else besides "value"?

 Dim lastCol As Long, arr

 arr = Range("Q145:AE211").Value
 
 lastCol = Cells(1, Columns.Count).End(xlToLeft).Column + 16
 
 Cells(1, lastCol).Resize(UBound(arr), UBound(arr, 2)).Value = arr
 
 End Sub
Solution

  • Try this way, but I don't understand why you add 16?

    Sub TestCopy()
   Dim lastCol As Long
   Range("Q145:AE211").Copy
   lastCol = Cells(1, Columns.Count).End(xlToLeft).Column + 16   ' why 16 ???
   Cells(1, lastCol).PasteSpecial xlPasteAll
   Cells(1, lastCol).PasteSpecial xlPasteColumnWidths
   Application.CutCopyMode = False
End Sub