| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy data
-- 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... Code:
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
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How copy none excel data & paste in 2007 without overwriting data | Excel Discussion (Misc queries) | |||
| filted data, copy and paste a col. puts data in wrong row how fix | New Users to Excel | |||
| Retrieve multiple data rows data from a very long list and copy t | Excel Discussion (Misc queries) | |||
| Copy old Data from web query while keeping previous days data | Excel Worksheet Functions | |||
| Copy data into cells until changes trigger new copy | Excel Programming | |||
Linear Mode
