Copy & Paste to new sheet if cell contains data?

[roxnoxsox]

Hi sorry this probably has a really simple solution.


I have a lot of formulas in Sheet1 - columns D to G. I want the cells in D to G that do contain values to copy and paste as special values into Sheet 2 - cell A2.

This is because I have more formulas than actually needed in D-G as the data they are working with will vary in length. Is there a macro to do this?

Let me know if this doesn't make sense at all! Thanks for any help <3

[NickyC]

Hi, a couple of questions.

Are you trying to copy the values generated by the formulas in columns D:G in sheet1 onto sheet2?

If you do this into cell A2 you will wind up with only one value there, not all the values in the range D:G. Or, do you want to copy the values into successive cells, begining at cell A2?

[NickyC]

... if so try this, it copies the values of each used cell in columns D:G into successive rows in sheet 2, begining at cell A2




Sub copy_values()
Dim cc As Range, ISect As Range, x As Long
Set ISect = Intersect(Sheets(1).UsedRange, Sheets(1).Range("D:G"))
x = 2
For Each cc In ISect.Cells
If cc <> "" Then
Sheets(2).Cells(x, 1) = cc.Value
x = x + 1
End If
Next cc
End Sub

[roxnoxsox]

Thank you for your above response..

This is halfway to what I need! I'm trying to copy the values generated by the formulas in D:G into sheet2 - columns A to D. At the moment, it's just copying into A2 and downward rather than across.

Thank you!

[NickyC]

ok, try this



Sub copy_values2()
Dim cc As Range, ISect As Range, RRow As Long, CCol As Long
Set ISect = Intersect(Sheets(1).UsedRange, Sheets(1).Range("D:G"))
For Each cc In ISect.Cells
If cc <> "" Then
RRow = cc.Row
CCol = cc.Column
Sheets(2).Cells(RRow, CCol - 3) = cc.Value
End If
Next cc
End Sub