Copy Data from Sheet1 to Sheet2

[Shamsuddeen]

Hi All,

I need a macro to copy data from Sheet1 to Sheet2. the data in sheet1 houses in Range A5:D10.

But there could be some blank cells within the above range and those rows should not be copied to sheet2.



Thanks.

 

[Shamsuddeen]

any solution>

 

[MikeDBMan]

So if row 6 is all blank do you move the contents of row 7 up to row 6? Or what?

 

[Shamsuddeen]

In sheet2, the data from sheet1 should be copied in such a way that there should be no blank rows in between the records.

 

[MikeDBMan]

Try This:

Sub CopyData()
Dim DataArray(10, 4) As Variant
Dim TheLen As Long
Dim Fnd As Integer
Dim X, Y As Long

Sheets("Sheet1").Select
For X = 5 To 10
    For Y = 1 To 4
        DataArray(X, Y) = Cells(X, Y).Value
    Next
Next

Sheets("sheet2").Select
For X = 5 To 10
    Let TheLen = 0
    For Y = 1 To 4
        Let TheLen = TheLen + Len(DataArray(X, Y))
    Next
    If TheLen = 0 Then
    Else
        Fnd = Fnd + 1
        For Y = 1 To 4
            Cells(Fnd, Y).Value = DataArray(X, Y)
        Next
    End If
Next


End Sub

 

[Shamsuddeen]

Thanks. It works perfectly.

How about if I want to ignore copying the rows from sheet1 whose any one or more cells are empty.

Thanks

 

[MikeDBMan]

This would do that:

Sub CopyData()
Dim DataArray(10, 4) As Variant
Dim TheLen As Long
Dim Fnd As Integer
Dim X, Y As Long

Sheets("Sheet1").Select
For X = 5 To 10
    For Y = 1 To 4
        DataArray(X, Y) = Cells(X, Y).Value
    Next
Next

Sheets("sheet2").Select
For X = 5 To 10
    Let TheLen = 0
    For Y = 1 To 4
        If Len(DataArray(X, Y)) < 1 Then
            TheLen = 0
            Exit For
        Else
            Let TheLen = 1
        End If
    Next
    If TheLen = 0 Then
    Else
        Fnd = Fnd + 1
        For Y = 1 To 4
            Cells(Fnd, Y).Value = DataArray(X, Y)
        Next
    End If
Next


End Sub

 

[Shamsuddeen]

Great!!!!!!

Thanks Mike.