Macro for copy and paste nonempty cells

denton

New Member
Joined
Oct 17, 2006
Messages
20
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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Here is a starter:

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

New Member
Joined
Oct 17, 2006
Messages
20
Here is a starter:

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

denton

New Member
Joined
Oct 17, 2006
Messages
20

ADVERTISEMENT

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
 

denton

New Member
Joined
Oct 17, 2006
Messages
20

ADVERTISEMENT

Error in declaration on which line?

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

denton

New Member
Joined
Oct 17, 2006
Messages
20
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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)
 

Forum statistics

Threads
1,136,263
Messages
5,674,710
Members
419,521
Latest member
Jasonnie

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