VBA Code to Copy/Paste Values of Cells that have been Recalculated

[wezoss]

Hello,

I'm new to the forum, but have seen great responses and content in here.

My problem is this: I have a spreadsheet that has cells that are being calculated based on formulas which are pulling data in from other worksheets and I would like the data that has been pulled in to be instantly copied and pasted with values to remove the formulas. All of the cells with formulas in the range (A36:AN55) cannot be copied/pasted at once because the data will only be pulled in one line at a time from multiple different worksheets.

I have toiled with the Worksheet_Change(ByVal Target As Range) and Worksheet_Calculate() events and have been unsuccesful since I am a rookie in VBA. Is this even possible to do? Any help would be greatly appreciated!

Thanks!

 

[AD_Taylor]

Not sure I understand what you mean by,

the data will only be pulled in one line at a time from multiple different worksheets.

You can definitely use copy and paste values in VBA but I can't put anything down yet as I don't think i understand what you're trying to do very well.

Can you re-word maybe and give me a bit more info?

 

[wezoss]

Sorry about that. I have 20 lines, 40 columns wide, that are pulling in data based on formulas that refer to file paths that are in 20 different cells. The formulas are using the INDIRECT formula to pull in the data based on the file path that is being entered into the 20 other reference cells. Once a file path is entered into the reference cell, the one row that is 40 columns wide (pulling in 40 different pieces of information from that file) is automatically populated instead of having to copy/paste each piece automatically.

What I would like to happen is that once the file path is entered into the reference cell, the data would automatically populate in the single row and a macro would run automatically to remove the formulas and leave only values so that once the reference file is closed, the data will remain as is.