paste special in vba not working

[chahine]

Hello excel community

hope all of you are doing well

i have a concern about paste special, i am doing a file where i will have different sheets created based on states

now when i use paste special to copy/paste headers in my new sheets created, only values are pasted without any formatting although am using xlPasteValuesAndNumberFormats

on the other hand when i use another method 'Range("a1:j1").Copy ws.Range("a1"), values & formats are pasted, for formats i mean the heading color

why is that? i am attaching the code and i commented the lines that work for pasting & the lines that dont, the procedure is called creatingsheets

please advise and thanks in advance

[#2]

@chahine 

As the name of the constant xlPasteValuesAndNumberFormats suggests, it causes Excel to paste values and number formats, but not any other formatting such as font name, font size, font color, fill color and borders. Sine the header row of the data sheet has General as number format, you're effectively only pasting values. If you want to paste formatting too, simply use

ws.Range("a1").PasteSpecial

[#3]

@Hans Vogelaar thank hans, one more question, after everything is copied, in all my new sheets created, i have last row selected, how to deselect it? i tried to put at end of code range("a1").select to select some other thing, but it didnt work, any suggestion?

[#4 · Accepted solution]

@chahine 

PasteSpecial selects the range you paste to.

If you use range.Copy Destination:=other_range, that does not happen, so it would be better to use that.

[#5]

Thanks Hans, these small things that i like to learn !