0
     Sub Button1_Click()

     For i = 1 To 6
     Worksheets("SHIPMENT OI").Range("B2:B50").Select
     Selection.Copy
     Worksheets("SHIPMENT OI").Range("B51").Select

     Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True
     Worksheets("SHIPMENT OI").Range("B50").Select
     Application.CutCopyMode = False
     Next

     End Sub

Hi Everyone,

may i know how do i maintain the code above and copy my cells color together?

aiming to transpose my cells and place them below along with the cells color/format.

Need some advice

Thanks in advance!

VBA Transpose Data done but cells copied without its cell color

Rahman
  • 1
  • 2
  • 1
    Have you tried using `Paste:=xlPasteAll`. See https://learn.microsoft.com/en-us/office/vba/api/excel.xlpastetype. Also: Don't use `Select`. Obligatory link to https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – FunThomas Feb 02 '22 at 09:56
  • Thank you so much Thomas! appreciate it! Hmm okay I shall take a look at the link you shared. I followed this code from YouTube, really know nuts about VBA but slowly learning on it. Once again thank you! – Rahman Feb 02 '22 at 10:01
  • Why do you have a loop (from 1 to 6)? You are executing the same Copy&Paste 6 times! And you should use `Option Explicit` and declare all your variables – FunThomas Feb 02 '22 at 10:26

0 Answers0