Forum Discussion
paste special in vba not working
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
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.
4 Replies
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
HansVogelaar 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?
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.
