Solved: Macro to copy and paste special values

[ram117]

Hi Everybody,:hi:


I need a macro to copy values from sheet1 and paste them in the Sheet3 values only. Here I'm giving my specific problem.

From Sheet1 Cells B4,N4,R4,W4,B5,&N5 which are text values to copied and pasted in sheet3.
Thaen again from Sheet1 Ranges B24:B1203,c24:c1203,......upto w24:w1203, except g24:g1203,I24:I1203,L24:L1203 these three ranges should be exclede from the selection in bto W range, all the cells in the range are having conditional formulas, if the condition is satisfied it will return a numeric value if the condition is not satisfied it will return "FALSE" So now I need only the values to be copied and paste special as values in Sheet3.
If some body can help for this macro it would be of a great help to me.

Thanks and Regards

Ram

[Ken Puls]

Hi Ram, and welcome to VBAX!

I'll give you the syntax for the first one. You may be able to apply it down the list...

Worksheets("Sheet1").Range("B4").Copy
Worksheets("Sheet3").Range("B4").Pastespecial Paste:=xlvalues

Now, on the condition part do you need to evaluate each cell individually? I haven't tested this, but you could try:

Sub test()
Dim cl As Range
'Turn off screen flashing for speed
Application.ScreenUpdating = False

For Each cl In Worksheets("Sheet1").Range("B24:B1203")
If cl.Value <> False Then
cl.Copy
Worksheets("Sheet3").Range(cl.Address).PasteSpecial Paste:=xlValue
End If
Next cl

'Resume screen updates
Application.ScreenUpdating = True
End Sub

HTH,

[ram117]

Hi,

The code for the conditional part you have given is working, but there is a problem, in the range I select thae no.of value will be changing every time when ever I change my input. the problem is once run the code its copying the values to sheet3(say B24 to B100). once I change my input value the values in the range B change to B24 to B900 then agian I run the code then B24 to B900 are getting copied but the old values from B900 to B100 are remaning same< but I don't want those values. So This means wheen ever I want to run the code the sheet3 should be cleared of data and fresh data to be copied.

Thanks and Regards

Ramana

[Ken Puls]

Hi Ramana,

Try this:

Sub test()
Dim cl As Range
'Turn off screen flashing for speed
Application.ScreenUpdating = False

For Each cl In Worksheets("Sheet1").Range("B24:B1203")
If cl.Value <> False Then
cl.Copy
Worksheets("Sheet3").Cells.ClearContents
Worksheets("Sheet3").Range(cl.Address).PasteSpecial Paste:=xlValue
End If
Next cl

'Resume screen updates
Application.ScreenUpdating = True
End Sub

[Zack Barresse]

Also, instead of using the copy method, you could go with a more (IMHO) elegant approach ..
Worksheets("Sheet1").Range("B4").Value = Worksheets("Sheet1").Range("B4").Value

[ram117]

Hi Ken,

Thankyou for the reply, its working, but still I have a problem with 4rows 2 of them are displaying #VALUE! if the condition is not satisfied and 2 Of them are displaying #N/A(result from lookup formula), These 4 rows are not getting copied by the vba code its giving me the error 'type Mismatch' .Here I'm giving the full code what I have written, Pl look into this and suggest me what further I have to do.

Sub test()
Worksheets("calculation").Range("b4").Copy
Worksheets("result").Range("b4").PasteSpecial Paste:=xlValues
Worksheets("calculation").Range("b5").Copy
Worksheets("result").Range("b5").PasteSpecial Paste:=xlValues
Worksheets("calculation").Range("n5").Copy
Worksheets("result").Range("n5").PasteSpecial Paste:=xlValues
Worksheets("calculation").Range("r5").Copy
Worksheets("result").Range("r5").PasteSpecial Paste:=xlValues
Worksheets("calculation").Range("w5").Copy
Worksheets("result").Range("w5").PasteSpecial Paste:=xlValues
Dim c1 As Range, c2 As Range, c As Long, c3 As Range
'turn off screen flashing for speed
c = Application.Calculation
Application.Calculation = xlManual
Application.ScreenUpdating = False
For Each c1 In Worksheets("calculation").Range("b24:b1203,d24:d1203,e24:e1203,f24:f1203,h24:h1203,j24:j1203,k24:k1203,m24:m 1203,n24:n1203,o24:o1203,p24:p1203,u24:u1203,v24:v1203")
Worksheets("result").Range(c1.Address).ClearContents
If c1.Value <> False Then
c1.Copy
Worksheets("result").Range(c1.Address).PasteSpecial Paste:=xlValues
Worksheets("result").Range(c1.Address).PasteSpecial Paste:=xlFormats
End If
Next c1
For Each c2 In Worksheets("calculation").Range("w24:w1203,t24:t1203")
Worksheets("result").Range(c2.Address).ClearContents
If c2.Value <> #Value! Then
c2.Copy
Worksheets("result").Range(c2.Address).PasteSpecial Paste:=xlValues
Worksheets("result").Range(c2.Address).PasteSpecial Paste:=xlFormats
End If
Next c2
For Each c3 In Worksheets("calculation").Range("c24:c1203,r24:r1203")
Worksheets("result").Range(c3.Address).ClearContents
If c3.Value <> #N/A Then
c3.Copy
Worksheets("result").Range(c3.Address).PasteSpecial Paste:=xlValues
Worksheets("result").Range(c3.Address).PasteSpecial Paste:=xlFormats
End If
Next c3
'resume screen updates
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
Application.Calculation = c
Application.CutCopyMode = False
End Sub


The range C2 and C3 I defined are not getting copied. It is saying type mismatch for boyh #VALUE! and #N/A what can be done here.

Thanks and Regards

Ramana

[Ken Puls]

hi Ramana,

Try evaluating the error codes using something like the following:
if iserror(c2.value) then ...

This will trap all #value, #N/A, #Div/0 and any other errors though, so you'll need to apply it carefully.

HTH,

[ram117]

Hi ken,

Thankyou verymuch for the support and help. It solved my problem.

Thanks and Regards

Ramana

[Ken Puls]

Glad to, Ramana! :)