Thread: Copy data

Here in this code i am copy data from 1 sheet to other sheet, but there is no
data in Finish per day sheet than also it's copy into Consumption sheet. how
do i copy only data which is available.

Sub Register_Finish_Per_Day()

Dim lrow As Long

lrow = Sheets("Finish Per Day").Cells(Rows.Count, "A").End(xlUp).Offset(1,
0).Row

Worksheets("Finish Per Day").Range("A" & lrow).Value = _
Worksheets("Consumption Per Day").Range("A2").Value

Worksheets("Finish Per Day").Range("B" & lrow).Value = _
Worksheets("Consumption Per Day").Range("A4").Value

Worksheets("Finish Per Day").Range("C" & lrow).Value = _
Worksheets("Consumption Per Day").Range("B4").Value

lrow = Sheets("Finish Per Day").Cells(Rows.Count, "A").End(xlUp).Offset(1,
0).Row

Worksheets("Finish Per Day").Range("A" & lrow).Value = _
Worksheets("Consumption Per Day").Range("A2").Value

Worksheets("Finish Per Day").Range("B" & lrow).Value = _
Worksheets("Consumption Per Day").Range("A5").Value

Worksheets("Finish Per Day").Range("C" & lrow).Value = _
Worksheets("Consumption Per Day").Range("B5").Value

lrow = Sheets("Finish Per Day").Cells(Rows.Count, "A").End(xlUp).Offset(1,
0).Row

Worksheets("Finish Per Day").Range("A" & lrow).Value = _
Worksheets("Consumption Per Day").Range("A2").Value

Worksheets("Finish Per Day").Range("B" & lrow).Value = _
Worksheets("Consumption Per Day").Range("A6").Value

Worksheets("Finish Per Day").Range("C" & lrow).Value = _
Worksheets("Consumption Per Day").Range("B6").Value

end sub

Thanks
Tiya

--
The major part of getting the right answer lies in asking the right
question...


"Tiya" wrote:

> Here in this code i am copy data from 1 sheet to other sheet, but there is no
> data in Finish per day sheet than also it's copy into Consumption sheet. how
> do i copy only data which is available.
>
> Sub Register_Finish_Per_Day()
>
> Dim lrow As Long
>
> lrow = Sheets("Finish Per Day").Cells(Rows.Count, "A").End(xlUp).Offset(1,
> 0).Row
>
> Worksheets("Finish Per Day").Range("A" & lrow).Value = _
> Worksheets("Consumption Per Day").Range("A2").Value
>
> Worksheets("Finish Per Day").Range("B" & lrow).Value = _
> Worksheets("Consumption Per Day").Range("A4").Value
>
> Worksheets("Finish Per Day").Range("C" & lrow).Value = _
> Worksheets("Consumption Per Day").Range("B4").Value
>
> lrow = Sheets("Finish Per Day").Cells(Rows.Count, "A").End(xlUp).Offset(1,
> 0).Row
>
> Worksheets("Finish Per Day").Range("A" & lrow).Value = _
> Worksheets("Consumption Per Day").Range("A2").Value
>
> Worksheets("Finish Per Day").Range("B" & lrow).Value = _
> Worksheets("Consumption Per Day").Range("A5").Value
>
> Worksheets("Finish Per Day").Range("C" & lrow).Value = _
> Worksheets("Consumption Per Day").Range("B5").Value
>
> lrow = Sheets("Finish Per Day").Cells(Rows.Count, "A").End(xlUp).Offset(1,
> 0).Row
>
> Worksheets("Finish Per Day").Range("A" & lrow).Value = _
> Worksheets("Consumption Per Day").Range("A2").Value
>
> Worksheets("Finish Per Day").Range("B" & lrow).Value = _
> Worksheets("Consumption Per Day").Range("A6").Value
>
> Worksheets("Finish Per Day").Range("C" & lrow).Value = _
> Worksheets("Consumption Per Day").Range("B6").Value
>
> end sub
>
> Thanks
> Tiya

Hi Tiya,

I'm not sure of your intent here... you're getting your data from
"Consumption Per Day" and the range you're copying to on "Finish Per Day"
will always be empty, don't you mean if there is no data on the "Consumption
Per Day" worksheet? If so, try this...

Sub Register_Finish_Per_Day()
Dim lrow As Long
With Sheets("Finish Per Day")
If WorksheetFunction.CountA(Sheets("Consumption Per
Day").Range("A2", "A4:A6", "B4:B5")) <> 0 Then
lrow = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
.Range("A" & lrow, "A" & lrow + 2) = Sheets("Consumption
Per Day").Range("A2").Value
.Range("B" & lrow, "B" & lrow + 2) = Sheets("Consumption
Per Day").Range("A4:A6").Value
.Range("C" & lrow, "C" & lrow + 2) = Sheets("Consumption
Per Day").Range("B4:B6").Value
End If
End With
End Sub