Excel Vba paste special values and formulas together

praveenpatel421983

New Member
Joined
Aug 17, 2017
Messages
41
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
 
Upvote 0
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.

Code:
Selection.PasteSpecial xlPasteValues
Selection.PasteSpecial xlPasteFormulas
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,895
Messages
6,175,257
Members
452,625
Latest member
saadat28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top