Copy cell to next sheet in next available row
 

I have a two sheet workbook. The first sheet named "Financial_Data" performs
the calculations and saves the total to a cell named PTotal. I am trying to
develop a macro to open the next sheet (Historical_Data), insert the date,
then move into the next column and paste the PTotal cell contents.
Everytime the macro is run, preferably daily, I would like it to drop to the
next empty row and paste the current info.
When I run the macro below, it overwrites what was previously there.
Can anyone help with this?

Sub HistDataMove()

Sheets("Historical_Data").Select
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=TODAY()"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=PTotal"
ActiveCell.Offset(1, -1).Range("A1").Select
End Sub

Copy cell to next sheet in next available row
 

Try something like this:

Sub HistDataMove()
Sheets("Historical_Data").Select
nextemptyrow = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A" & nextemptyrow) = Date
Range("B" & nextemptyrow) = Range("PTotal")
Sheets("Financial_Data").Select
End Sub

Regards,
Stefi


€žjdub€� ezt Ã*rta:

I have a two sheet workbook. The first sheet named "Financial_Data" performs
the calculations and saves the total to a cell named PTotal. I am trying to
develop a macro to open the next sheet (Historical_Data), insert the date,
then move into the next column and paste the PTotal cell contents.
Everytime the macro is run, preferably daily, I would like it to drop to the
next empty row and paste the current info.
When I run the macro below, it overwrites what was previously there.
Can anyone help with this?

Sub HistDataMove()

Sheets("Historical_Data").Select
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=TODAY()"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=PTotal"
ActiveCell.Offset(1, -1).Range("A1").Select
End Sub

Copy cell to next sheet in next available row
 

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You send a clear explanation of what you want
3. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jdub" wrote in message
...
I have a two sheet workbook. The first sheet named "Financial_Data"
performs
the calculations and saves the total to a cell named PTotal. I am trying
to
develop a macro to open the next sheet (Historical_Data), insert the date,
then move into the next column and paste the PTotal cell contents.
Everytime the macro is run, preferably daily, I would like it to drop to
the
next empty row and paste the current info.
When I run the macro below, it overwrites what was previously there.
Can anyone help with this?

Sub HistDataMove()

Sheets("Historical_Data").Select
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=TODAY()"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=PTotal"
ActiveCell.Offset(1, -1).Range("A1").Select
End Sub