Could be an easy fix. Please help

[Dan88]

Hello, I am having difficulty figuring out this VBA code. What I need it to do is to only apply the copy and paste to visible cells or cells with data within the given range, however this coding copies and paste values to all cells listed in the range. I.E. i have data from lines A6 to AZ11.

When i run the VBA the code should only copy and paste value to rows 6-11 not 6-20.
Any one have any good suggestions?

Thanks in advance.


Sub Copy()


Dim Rng As Range
Set Rng = Selection.SpecialCells(xlCellTypeVisible)
Rng.Copy


Range("CE6:CE20").Value = Range("CD6:CD20").Value
Range("AF6:AF20").Value = Range("AG6:AG20").Value


End Sub

 

[Meggesto]

If you adjust your code like this:

Range("CE6:CE11").Value = Range("CD6:CD11").Value
Range("AF6:AF11").Value = Range("AG6:AG11").Value

You'll take the values of rows 6 -11 in Column CD and place them in rows 6 - 11 of CE, and the same logic for AG to AF.
It looks like you may be trying to do something else with the code but I'm not sure what exactly but this should fix one problem and we can try to solve the next after.

 

[Dan88]

Meggesto, thanks for the reply.

The data is all in a table and I can be adding new data within the table daily or every other day. Those cells I specified have a formula in it and im trying to get it to be values in the column over.

So today I may have data in the table from Row 6 – 11 but tomorrow I may have data from row 6 -13. I just need the vba to copy and paste the formula to value on rows that contains data and not the entire specified range or table.

Does this make sense?

If you adjust your code like this:

Range("CE6:CE11").Value = Range("CD6:CD11").Value
Range("AF6:AF11").Value = Range("AG6:AG11").Value

You'll take the values of rows 6 -11 in Column CD and place them in rows 6 - 11 of CE, and the same logic for AG to AF.
It looks like you may be trying to do something else with the code but I'm not sure what exactly but this should fix one problem and we can try to solve the next after.

 

[Meggesto]

So maybe something like this:
It's based on you first selecting all the cells that have formulas you'd like to be values.

Sub CopyValue()

Dim FormulaRange As Range
Set FormulaRange = Selection

Selection.Copy
Selection.Offset(0, 1).PasteSpecial xlPasteValues


End Sub

So this would just simply copy the values you've selected from one column that are calculated by formulas and then paste them to the column directly next to them as Values.

 

[Dan88]

Thank you for continuing to help me with this.

Is there a way to do this without selecting the cells manually? I thought the visible code i use above should work but it adds too many unwanted rows.

Thanks!!

So maybe something like this:
It's based on you first selecting all the cells that have formulas you'd like to be values.

Sub CopyValue()

Dim FormulaRange As Range
Set FormulaRange = Selection

Selection.Copy
Selection.Offset(0, 1).PasteSpecial xlPasteValues


End Sub

So this would just simply copy the values you've selected from one column that are calculated by formulas and then paste them to the column directly next to them as Values.

 

[Meggesto]

We would just define the range that the cells are in that you need copied and pasted as values. If they're always the same cells or contained with a range of cells then we can define the range rather than use Selection.

So for Example:

Dim FormulaRange As Range
Set FormulaRange = Range("CE6:CE20")

FormulaRange.Copy
FormulaRange.Offset(0, 1).PasteSpecial xlPasteValues

Now this takes all the formula outputs in Column CE Row 6 to Column CE Row 20 and pastes them one column over directly next to the outputs but as solid values.
We just adjust the range to the one that your data is in.