Hi In below macro i have problem with that it change the format. Its a part of other codes and i need it to be copied to sheet "Robot" in the original format from column C8:C29 in Sheet Spain. Code below is from Marc and its brilliant, but i cant figure out to change this.
I have attached a test sheet, so you can see how its formatted now. in column C sheet Spain. Specially i really need all the leading zeroes to get copied. But i need it to keep the formatting.
Please have a look
Sincerely
Abjac
Sub spainrobotsheet()
Dim i As Long, j As Long, myrow As Long, myval As Variant
myrow = Sheets("Robot").Cells(Rows.Count, "A").End(xlUp).Row + 1
With Sheets("Spain")
Sheets("Robot").Cells(myrow, 1) = .Range("C8")
Sheets("Robot").Cells(myrow, 2) = .Range("C9")
Sheets("Robot").Cells(myrow, 3) = .Range("C11")
Sheets("Robot").Cells(myrow, 4) = .Range("C12")
Sheets("Robot").Cells(myrow, 5) = .Range("C13")
Sheets("Robot").Cells(myrow, 6) = .Range("C14")
Sheets("Robot").Cells(myrow, 7) = .Range("C15")
Sheets("Robot").Cells(myrow, 8) = .Range("C16")
Sheets("Robot").Cells(myrow, 9) = .Range("C17")
Sheets("Robot").Cells(myrow, 11) = .Range("C19")
Sheets("Robot").Cells(myrow, 12) = .Range("C20")
Sheets("Robot").Cells(myrow, 13) = .Range("C21")
Sheets("Robot").Cells(myrow, 14) = .Range("C22")
Sheets("Robot").Cells(myrow, 15) = .Range("C23")
Sheets("Robot").Cells(myrow, 16) = .Range("C24")
Sheets("Robot").Cells(myrow, 17) = .Range("C25")
Sheets("Robot").Cells(myrow, 18) = .Range("C26")
myval = Split(.Cells(29, "C"), ",")
.Cells(8, "C").Resize(22, 1).ClearContents 'only if you want to "Cut"
For j = LBound(myval) To UBound(myval)
Sheets("Robot").Cells(myrow, 19 + j) = myval(j) 'S is 19th
Next j
End With
End Sub
Bookmarks