transfering all data.
 

I have a code where I transfer daily data from €œConsumption Per Day€� sheets
to €œFinish Per Day€� sheets its working nice. But I have one problem i.e.
It transfers data like date from A2 cell each time so if there are no data
in Consumption per day sheet it transfer date all the time.
Is there any way it should not transfer data if there is no data?

Any help.

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

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

transfering all data.
 

Sub No_Empty_Cells()

If ActiveCell.Value < "" Then MsgBox ActiveCell.Value

End Sub


--
H.A. de Wilde
------------------------------------------------------------------------
H.A. de Wilde's Profile: http://www.excelforum.com/member.php...o&userid=30679
View this thread: http://www.excelforum.com/showthread...hreadid=538806

transfering all data.
 

Put in a check before you transfer the data

I don't know what cell(s) to check, but for example:

Sub Register_Finish_Per_Day()
Dim rng1 as Range, rng2 as Range, rng3 as Range
Dim sh1 as Worksheet, sh2 as Worksheet
Dim lrow As Long


set sh = Worksheets("Consumption Per Day")
set sh1 = Worksheets("Finish Per Day")
set rng1 = sh.Range("A4:B4")
set rng2 = sh.Range("A5:B5")
set rng3 = sh.Range("A6:B6")

if application.CountA(rng1) = 2 then
lrow = sh1.Cells(Rows.Count, "A").End(xlUp).Offset(1,0).Row
Sh1.Range("A" & lrow).Value = _
sh.Range("A2").Value

sh1.Range("B" & lrow).Value = _
sh.Range("A4").Value

sh1.Range("C" & lrow).Value = _
sh.Range("B4").Value
end if

if application.CountA(rng2) = 2 then
lrow = sh1.Cells(Rows.Count, "A").End(xlUp).Offset(1,0).Row

sh1.Range("A" & lrow).Value = _
sh.Range("A2").Value

sh1.Range("B" & lrow).Value = _
sh.Range("A5").Value

sh1.Range("C" & lrow).Value = _
sh.Range("B5").Value
End if

if Application.CountA(rng3) = 2 then
lrow = sh1.Cells(Rows.Count, "A").End(xlUp).Offset(1,0).Row

sh1.Range("A" & lrow).Value = _
sh.Range("A2").Value

sh1.Range("B" & lrow).Value = _
sh.Range("A6").Value

sh1.Range("C" & lrow).Value = _
sh.Range("B6").Value

End if
end sub

--
Regards,
Tom Ogilvy

"****al shah" wrote:

I have a code where I transfer daily data from €œConsumption Per Day€� sheets
to €œFinish Per Day€� sheets its working nice. But I have one problem i.e.
It transfers data like date from A2 cell each time so if there are no data
in Consumption per day sheet it transfer date all the time.
Is there any way it should not transfer data if there is no data?

Any help.

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

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