macro to save a values of a cell in a new sheet as a new row
 

hi all,

I need to copy the value of a cell in b9 and value of a cell in b19
from worksheet summary to a new work sheet historical data.

These cells are subject to change frequently, so i need to keep the
history data in teh new sheet.

In the new sheet it should look something like this:

Date Estimated effort

10-feb 23
11-feb 12 and so on.

please help me by giving the excel code to achieve this.Its really
urgent

Thanks in advance..


--
dpt
------------------------------------------------------------------------
dpt's Profile: http://www.excelforum.com/member.php...o&userid=25861
View this thread: http://www.excelforum.com/showthread...hreadid=392446

macro to save a values of a cell in a new sheet as a new row
 

hi,
i'm assuming that the new sheet will have the data in columns A and B. if
not you will have to edit the macro.
you will have to change the sheet names in the macro.

Sub macCaptureData()
Dim ccc As Range
Dim ccc2 As Range
Dim ppp As Range
Dim ppp2 As Range
Sheets("Yoursheet").Select
Set ccc = Range("B9")
Set ppp = Range("B19")
Set ccc2 = Sheets("newsheet").Range("A1").End(xlDown).Offset( 1, 0)
Set ppp2 = Sheets("newsheet").Range("B1").End(xlDown).Offset( 1, 0)
ccc2.Value = ccc.Value
ppp2.Value = ppp.Value
End Sub

regards
FSt1

"dpt" wrote:


hi all,

I need to copy the value of a cell in b9 and value of a cell in b19
from worksheet summary to a new work sheet historical data.

These cells are subject to change frequently, so i need to keep the
history data in teh new sheet.

In the new sheet it should look something like this:

Date Estimated effort

10-feb 23
11-feb 12 and so on.

please help me by giving the excel code to achieve this.Its really
urgent

Thanks in advance..


--
dpt
------------------------------------------------------------------------
dpt's Profile: http://www.excelforum.com/member.php...o&userid=25861
View this thread: http://www.excelforum.com/showthread...hreadid=392446

macro to save a values of a cell in a new sheet as a new row
 

<Dpt thanks a lot for the kind help, its simple and it worked.



FSt1 Wrote:
hi,
i'm assuming that the new sheet will have the data in columns A and B
if
not you will have to edit the macro.
you will have to change the sheet names in the macro.

Sub macCaptureData()
Dim ccc As Range
Dim ccc2 As Range
Dim ppp As Range
Dim ppp2 As Range
Sheets("Yoursheet").Select
Set ccc = Range("B9")
Set ppp = Range("B19")
Set ccc2 = Sheets("newsheet").Range("A1").End(xlDown).Offset( 1, 0)
Set ppp2 = Sheets("newsheet").Range("B1").End(xlDown).Offset( 1, 0)
ccc2.Value = ccc.Value
ppp2.Value = ppp.Value
End Sub

regards
FSt1

"dpt" wrote:


hi all,

I need to copy the value of a cell in b9 and value of a cell in b19
from worksheet summary to a new work sheet historical data.

These cells are subject to change frequently, so i need to keep the
history data in teh new sheet.

In the new sheet it should look something like this:

Date Estimated effort

10-feb 23
11-feb 12 and so on.

please help me by giving the excel code to achieve this.Its really
urgent

Thanks in advance..


--
dpt

------------------------------------------------------------------------
dpt's Profile
http://www.excelforum.com/member.php...o&userid=25861
View this thread
http://www.excelforum.com/showthread...hreadid=392446



--
dp
-----------------------------------------------------------------------
dpt's Profile: http://www.excelforum.com/member.php...fo&userid=2586
View this thread: http://www.excelforum.com/showthread.php?threadid=39244

macro to save a values of a cell in a new sheet as a new row
 

Hi,

I just modified the code which u sent to some thing like this and it
working as desired but there is disadvantage with it .

Sub macInsertData()
Dim estdate As Range
Dim dt As Range
Dim esteffort As Range
Dim effort As Range
Sheets("Summary").Select
Set dt = Range("B9")
Set effort = Range("B19")

Dim lastrow As Long
Worksheets("Historical Data").Activate
ActiveCell.SpecialCells(xlCellTypeLastCell).Select
lastrow = ActiveCell.Row
Cells(lastrow + 1, 1).Value = dt.Value
Cells(lastrow + 1, 2).Value = effort.Value
End Sub

This above code is working fine when are values row by row, but thi
fails when there are some values after several blank rows, i mean

row1------date1 effort1
row2------date2 effort2
row3------blank
row4------blank

macro to save a values of a cell in a new sheet as a new row
 

Try something like:

Sub macInsertData()
Dim estdate As Range
Dim dt As Range
Dim esteffort As Range
Dim effort As Range
Sheets("Summary").Select
Set dt = Range("B9")
Set effort = Range("B19")

Dim lastrow As Long
Worksheets("Historical Data").Activate
'ActiveCell.SpecialCells(xlCellTypeLastCell).Selec t
'lastrow = ActiveCell.Row
lastrow = dt.End(xlDown).Row + 1
Cells(lastrow + 1, 1).Value = dt.Value
Cells(lastrow + 1, 2).Value = effort.Value
End Sub




Manges

--
mangesh_yada
-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=39244

macro to save a values of a cell in a new sheet as a new row
 

mangesh_yadav Wrote:
Try something like:

Sub macInsertData()
Dim estdate As Range
Dim dt As Range
Dim esteffort As Range
Dim effort As Range
Sheets("Summary").Select
Set dt = Range("B9")
Set effort = Range("B19")

Dim lastrow As Long
Worksheets("Historical Data").Activate
'ActiveCell.SpecialCells(xlCellTypeLastCell).Selec t
'lastrow = ActiveCell.Row
lastrow = dt.End(xlDown).Row + 1
Cells(lastrow + 1, 1).Value = dt.Value
Cells(lastrow + 1, 2).Value = effort.Value
End Sub




Mangesh

Hi Mangesh,

Its not working quite right.If the 3 rows are filled and from 3rd row
to 12 rows is blank, it every time starts inserting rows from the 13th
row, dnt know , even i delete the previous rows it always starts from
the 13 th row.

Can you please determine the reason for it.i tried several other things
but no desired results.

I hope u get back with an answer, if u dnt mind u can try this code.

Thanks.


--
dpt
------------------------------------------------------------------------
dpt's Profile: http://www.excelforum.com/member.php...o&userid=25861
View this thread: http://www.excelforum.com/showthread...hreadid=392446

macro to save a values of a cell in a new sheet as a new row
 

I am assuming your first row is cell B9. The next is B10 and third i
B11. If this is the case, the cod eI provided should work as what i
does is basically start from cell B9. Then it replicates the key pres
"control + down_arrow_key" this takes it to cell B11. Add 1 to go t
the next row i.e. B12.
This is what my code is doing. Isn't this what it is supposed t
do...?

Manges

--
mangesh_yada
-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=39244

macro to save a values of a cell in a new sheet as a new row
 

No No No....the values of B9 and B19 from worksheet summary should b
copied to worksheet Historical Data as new row, each time a key i
pressed in the summary sheet.

In the Historical Sheet the first row available for inserting data i
row 4.

So it should be something like

row4.......date effort
row5.......date effort

and so on.

--
dp
-----------------------------------------------------------------------
dpt's Profile: http://www.excelforum.com/member.php...fo&userid=2586
View this thread: http://www.excelforum.com/showthread.php?threadid=39244

macro to save a values of a cell in a new sheet as a new row
 

in that case, change the line:
Set dt = Range("B9")
to
Set dt = Range("B4")
in your code.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=392446