A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Copy cell to next sheet in next available row



 
 
Thread Tools Display Modes
  #1  
Old September 24th, 2009, 05:53 AM posted to microsoft.public.excel.newusers
JDub
external usenet poster
 
Posts: 5
Default 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

  #2  
Old September 24th, 2009, 11:50 AM posted to microsoft.public.excel.newusers
Stefi
external usenet poster
 
Posts: 1,841
Default 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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:42 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.