Could be an easy fix. Please help

Dan88

Active Member
Joined
Feb 14, 2008
Messages
262
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Meggesto

Board Regular
Joined
Mar 8, 2016
Messages
216
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

Active Member
Joined
Feb 14, 2008
Messages
262
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

Board Regular
Joined
Mar 8, 2016
Messages
216
So maybe something like this:
It's based on you first selecting all the cells that have formulas you'd like to be values.

Code:
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.
 
Last edited:

Dan88

Active Member
Joined
Feb 14, 2008
Messages
262
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.

Code:
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

Board Regular
Joined
Mar 8, 2016
Messages
216
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:
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,387
Messages
5,595,879
Members
414,029
Latest member
mrwilker

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
Top