Macro change the format need to keep original

[abjac]

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

Please Login or Register  to view this content.

[HaHoBe]

Hi, abjac,

maybe try

Please Login or Register  to view this content.

Ciao,
Holger

[abjac]

Hi Holger nice to see you here again and thanks for the answer. It gave the same result. It copy and all are in text format, but it dont work because some in the column have a custom format. So the result was the same without the zeroes in front.
Try play with the test sheet, here you can see the format it its possible to format B8 to B29 to text so it keeps the zeroes infront, that was maybe a solution before make the copy to sheet Robot.

Have a look if time.

Sincerely
Abjac

[HaHoBe]

Hi,abjac,

I misssed to add the .Text. Please try

Please Login or Register  to view this content.

Ciao,
Holger

[abjac]

Hi Holger. It was fine it put the zeroes as input there now. But the result are not the same. The first macro made the correct result, but did not have the leading zeroes. This macro have the leading zeroes but it give the wrong result. Tried to figure out why, but cant see why its like that

You can see the result with the first code, its correct but try ur second and it add the zeroes but the result is not the same
Have a look in the new attached sheet. It jump over some of the input.

Have a look please

Sincerely

Abjac

[HaHoBe]

Hi, abjac,

my bad, I mixed the place where to put the variable lngHelp.

Loop should run from 1 to 18 (not 19 like in the last post) and please replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

and see if that fixes the issue.

Ciao,
Holger

[abjac]

Hi Holger that did the trick. The code just run perfect. Wow.. Thanks allot again for your brilliant help, its really appriciated much
Have a nice day.

Sincerely

Abjac