Transpose data - keep original formatting

[LostinVA]

Hi all -

I've got the below code to copy column data when a Command Button is pressed and paste/transpose on a different tab. Each time the button is pressed, the data is pasted on the next subsequent row.

Is there a way to paste/transpose the data but keep the original cell formatting where the data is being pasted? For example, I have a 'Country' drop-down box. When the data is pasted/transposed, it 'wipes' out the drop-down box.. Is there a way to paste/transpose but keep the drop-down box?

Private Sub CommandButton1_Click()
Range("B6:B41").Copy
Sheets("QRR Template").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Transpose:=True
End Sub

Appreciate any help!

 

[JoeMo]

If you are pasting values try:

With Sheets("QRR Template").Range("B" & Rows.Count).End(xlUp).Offset(1)
    .PasteSpecial xlPasteFormats, Transpose:=True
    .PasteSpecial xlPasteValues, Transpose:=True
End With

If you are pasting formulas change xlPasteValues to xlPasteFormulas

 

[LostinVA]

I love this forum. Thank you so much for your quick response. EXACTLY what I needed! Thank you!