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..

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
>
>

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

Originally Posted by FSt1

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
>
>

Hi,

I just modified the code which u sent to some thing like this and its 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 this fails when there are some values after several blank rows, i mean

row1------date1 effort1
row2------date2 effort2
row3------blank
row4------blank
.
.
.
.
.
.
.
.
.
.
.
.
.
.
row20-----date effort

if by mistake there is any value in the row 20 then next time the function is called the vlues will be inserted in the row 21 instead of row 5.

I hope i am clear with my problem.Please can annyone tell me how to solve this problem?

Any help is greatly appreciated.

thanks.

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).Select
'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

Originally Posted by mangesh_yadav

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).Select
'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.

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

Mangesh

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

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

So it should be something like

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

and so on..

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

Mangesh