Excel Vba paste special values and formulas together

[praveenpatel421983]

Hi,

I am still learning Vba. I have a question on pasting the data. This might be a silly question but I didn't found the solution anywhere. I also searched in this forum, please let me know if you remember any similar post.

I have a workbook which has lot of data manually updated by users. This data has values, strings and also formulas. There are many files like this. The workbook used by users doesn't have any required formatting. I have created a new workbook with same column headings and with required formatting like borders. I have created the required coding necessary for this new format workbook to paste values from the old workbook to new workbook. Along with that I have added code to change cell colors, conditional formatting and generate graphs. These workbooks will updated by users when required, so some of the columns have formulas which is linked to graphs. As the code pastes only values, cells which has formulas are converted to values. I need a code to copy both values and formulas together. Could someone please help me?

I also tried adding code for finding the column number and copying formulas to respective column number but there are different formats used for different work due to which I have to create macro for each project. I thought this can be avoided if I can paste both values and formulas together. Please help.

Thanks
Praveen

 

[Fluff]

If supply the code that you already have (using code tags, the # icon in the reply window).
You will have a better chance of getting some help

 

[praveenpatel421983]

I apologize for replying late.

I have selected the range and have used xlPasteValues and xlPasteFormulas. My code is big and have used this in multiple places. My code selects the columns individually and selected code is pasted using below mentioned codes. I am sorry if I am confusing you.

Selection.PasteSpecial xlPasteValues
Selection.PasteSpecial xlPasteFormulas