PDA

View Full Version : select/copy cells in a range that contain data



sunilmulay
10-06-2008, 03:15 AM
Hi All
I am a newbie here.
I want to create a macro to select only cells that contain data in the range("D74:BE74") and "paste special" their values (values only-not formulas) up 47 rows for each cell that contains data.

any idea how to do this?

Thanks in advance!
Sunil

MaximS
10-06-2008, 03:32 AM
try this:

Range("D74:BE74").Copy Destination:=Range("D47:BE73")
Range("D47:BE73").Formula = Range("D47:BE73").Value

This will copy Range D74:BE74 to 47 rows above the copied range and change values from formulas to values.
If cell is empty then destination cell will be empty too.

sunilmulay
10-06-2008, 03:54 AM
Hi Maxim - thanks for reply.
Your code seems to return 0 values into all the destination range. Let me put my problem another way for clarity.

Some cells in D74:BE74 contain formulas returning values.
Some cells in D27:BE27 also contain values.
I want the macro to scan D74:BE74 and if it finds a value there to copy it into the corresponding cell (same column) in D27:BE27. I don't want it to overwrite any other values in that range.

Hope this makes sense...

Thanks
S

MaximS
10-06-2008, 04:02 AM
So you want to copy only values not formulas?
Am I correct?

sunilmulay
10-06-2008, 04:58 AM
that's correct.

MaximS
10-06-2008, 05:17 AM
try this:

Sub CopyIfNotEmpty()

For i = 4 To 54

If Cells(74, i).Value <> "" And Cells(74, i).Value <> 0 Then

'Change 27 to row of your choice
Cells(27, i).Value = Cells(74, i).Value

End If

Next i

End Sub

sunilmulay
10-06-2008, 05:50 AM
Hi There
I've modified the code as follows:
I'm getting a "Compile Error - Variable Not defined" error and it stops on the 3rd line highlighting i.
???
Thanks

Sub PostProg01()
'
'
Sheets("Stage01-EV").Select
ActiveSheet.Unprotect Password:=PWORD
For i = 4 To 54

If Cells(74, i).Value <> "" And Cells(74, i).Value <> 0 Then

'Change 27 to row of your choice
Cells(27, i).Value = Cells(74, i).Value

End If

Next i
ActiveSheet.Protect Password:=PWORD
End Sub

MaximS
10-06-2008, 06:53 AM
then insert that:

Sub PostProg01()
'
Dim i As Long
Sheets("Stage01-EV").Select
ActiveSheet.Unprotect Password:=PWORD

and then the rest

sunilmulay
10-06-2008, 04:40 PM
that worked a treat thanks.
I will now try and expand it to cover some other items I want to be copied....
Sunil