# Macro for copy and paste nonempty cells

#### denton

##### New Member
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
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
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

#### Andrew Poulsom

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

#### denton

##### New Member

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

##### MrExcel MVP
Error in declaration on which line?

#### denton

##### New Member

Error in declaration on which line?

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

#### Andrew Poulsom

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

#### denton

##### New Member
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
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)

Replies
3
Views
100
Replies
4
Views
157
Replies
5
Views
355
Replies
1
Views
404
Replies
1
Views
134

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.

### Which adblocker are you using?

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

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