Copy Data from Sheet1 to Sheet2

Shamsuddeen

Active Member
Joined
Feb 16, 2002
Messages
292
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.
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

MikeDBMan

Well-known Member
Joined
Nov 10, 2010
Messages
589
So if row 6 is all blank do you move the contents of row 7 up to row 6? Or what?
 

Shamsuddeen

Active Member
Joined
Feb 16, 2002
Messages
292
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

Well-known Member
Joined
Nov 10, 2010
Messages
589

ADVERTISEMENT

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

Active Member
Joined
Feb 16, 2002
Messages
292
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

Well-known Member
Joined
Nov 10, 2010
Messages
589
This would do that:
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,850
Members
414,342
Latest member
K Darrell Smith

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