I just found that the result of a horizontal Paste can take 2 forms depending
on the relation of the number of copied columns to the number of columns in
the destination range:
€¢ If the number of destination columns eg 16384 is divisible without
remainder by the number of copied columns eg 32 (result = 512) then the
copied columns will be pasted 512 times next to each other, covering the
whole destination range.
€¢ Otherwise they will be pasted only exactly once padded with empty cells at
the right.

Being a coding novice I created below piece of code with the intention to
paste the copied range only once but it does it 512 times. How can I improve
my code?
Sheets("Sheet1").Range("A10:AF10").Copy
Sheets("Sheet2").Rows(1).PasteSpecial (xlPasteValues)