Macro for copy and paste nonempty cells

[denton]

Hi,

do you think you can help me?
In sheet2 I have in columns C and D some numbers. but numbers arent in all cells, eg C1 is numbered,C2 empty,C3-C6 numbered,C7 empty,C8 numbered etc. The same for column D.

I need to make some macro which will copy only cells with numbers from column C to sheet1 (=will copy cells C1,C3-C6,C8 from sheet2), but...in sheet1 i have table 40x4 cells (C1:F40). Imagine that macro will find cca 100 numbered cells from columnd C in sheet2 - i need to put these numbers to table C1:F40 in sheet1 ->first 40 numbers will be in C1:C40, the 41st will be in D1 and so on,81 st in E1. It is easy to make it handy, but I'd like to make some macro.
The same way for column D in sheet2 -> copy only numbered cells from Sheet2 to Sheet1,column A

Would be great if you can help me.

 

[Andrew Poulsom]

Here is a starter:

Sub Test()
    Dim Sh1 As Worksheet
    Dim Sh2 As Worksheet
    Dim Rng As Range
    Dim r As Integer
    Dim c As Integer
    Dim Cell As Range
    Set Sh1 = Worksheets("Sheet1")
    Set Sh2 = Worksheets("Sheet2")
    Set Rng = Sh2.Columns("C:C").SpecialCells(xlCellTypeConstants, 1)
    r = 1
    c = 3
    For Each Cell In Rng.Cells
        If r > 40 Then
            r = 1
            c = c + 1
        End If
        Sh1.Cells(r, c).Value = Cell.Value
        r = r + 1
    Next Cell
End Sub

 

[denton]

Here is a starter:

Sub Test()
    Dim Sh1 As Worksheet
    Dim Sh2 As Worksheet
    Dim Rng As Range
    Dim r As Integer
    Dim c As Integer
    Dim Cell As Range
    Set Sh1 = Worksheets("Sheet1")
    Set Sh2 = Worksheets("Sheet2")
    Set Rng = Sh2.Columns("C:C").SpecialCells(xlCellTypeConstants, 1)
    r = 1
    c = 3
    For Each Cell In Rng.Cells
        If r > 40 Then
            r = 1
            c = c + 1
        End If
        Sh1.Cells(r, c).Value = Cell.Value
        r = r + 1
    Next Cell
End Sub

don't know, but doesn't work , can you help me what should i do with this? Have Office 2000

 

[Andrew Poulsom]

It worked for me when I tested it. In what way doesn't it work for you?

 

[denton]

It worked for me when I tested it. In what way doesn't it work for you?

error in declaration...can you send me your test? otis(at)centrum.cz

 

[Andrew Poulsom]

Error in declaration on which line?

 

[denton]

Error in declaration on which line?

9th line, even if I renamed my sheet names to Sheet1 and Sheet2

 

[Andrew Poulsom]

Do you have worksheets named Sheet1 and Sheet2 in the ActiveWorkbook?

 

[denton]

Do you have worksheets named Sheet1 and Sheet2 in the ActiveWorkbook?

Strange...now trying it at home /office 2003/ and that mistake is over, but now it says: No cells found and this row appear:
Set Rng = Sh2.Columns("C:C").SpecialCells(xlCellTypeConstants, 1)

Please can you send me your test (excel file) to my email ?

 

[Andrew Poulsom]

You said "In sheet2 I have in columns C and D some numbers". Are those numbers constants or the result of formulas? My code assumes they are constants. If they are the result of formulas try changing:

Set Rng = Sh2.Columns("C:C").SpecialCells(xlCellTypeConstants, 1)

to:

Set Rng = Sh2.Columns("C:C").SpecialCells(xlCellTypeFormulas, 1)