copying value of cell to another sheet

  • Thread starter Thread starter Sarah
  • Start date Start date
S

Sarah

Hi
I'm hoping someone can help me...
I have a worksheet called ENTRY
When you enter quantities it adds up with a total sum at
the bottom
I created a macro to clear the quantity column so I can
start over again.
But I want to record the total sum on another worksheet.
When i do it now it just replaces the info in the same
cell - I want it to enter the next value into the next
cell..
Any help appreciated.
It would also be nice to have the same macro do both -
copy the value, then clear the contents...
Here is the macro

Sub ClearContents()
'
' ClearContents Macro
' Macro recorded 02/10/2003 by SARAH RANSOME
'
' Keyboard Shortcut: Ctrl+j
'
Range("G2:G59").Select
Selection.ClearContents
End Sub
 
Sarah, try something like this, with your totals in G60

Sub ClearContents()
'
' ClearContents Macro
' Macro recorded 02/10/2003 by SARAH RANSOME
'
' Keyboard Shortcut: Ctrl+j
'
Range("G2:G59").ClearContents
'Will put the valve in sheet1 G60 in sheet 2 column A in the next cell down
Sheet2.Range("A65536").End(xlUp).Offset(1, 0) =
Sheet1.Range("G60").Value
End Sub

--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
HI
Thanks so much for the quick response
I'm getting it to clear the contents but not copy the
value...
I get an error in the expression..
It doesn't seem to like the OffSet(1, 0) =


Sub ClearContents()
'
' ClearContents Macro
' Macro recorded 02/10/2003 by SARAH RANSOME
'
' Keyboard Shortcut: Ctrl+j
'
Range("G2:G59").ClearContents
'Will put the value in sheet 1 G60 in sheet 2 column A in
the next cell down
Sheet2.Range("A65536").End(xlUp).Offset(1, 0) =
Sheet2.Range("G63").Value


End Sub
 
I just tested this part of the code and it worked fine for sheet2. Maybe you
forgot the continuation character of a space and underscore at the end of a
line you want to continue. Notice mine below after the =
 
Hi
This is what I've got :
I'm still getting an error
I added the continuation space and still can't get it to
work.. Any suggestions??


'Will put the value in sheet 1 G63 in sheet 2 column A in
the next cell down
Sheet2.Range("A65536").End(xlUp).Offset(1, 0) = _
Sheet2.Range("G63").Value
Range("G2:G62").ClearContents
 
I'm not sure why you're getting the error--but I don't think it's the offset.

Here's my version (just slightly formatted):

Option Explicit
Sub MyClearContents()
Sheet2.Range("A65536").End(xlUp).Offset(1, 0).Value _
= Sheet1.Range("G63").Value
Sheet1.Range("G2:G62").ClearContents
End Sub

I changed the "sending" cell to point at sheet1. And I added Sheet1 to the
front of the .clearcontents line.

Are you getting a "Run time error - 424, Object required"? error?

If yes, then maybe it's because you don't have the worksheets that you refer to
in your code.

You're using the codenames within your code. When you're in the VBE, you can
look at the project explorer to verify the codename.

There's a section called "microsoft excel objects". Under that, you'll see the
worksheets:
xxxxxxx (yyyyyy)

You have to have sheet1 and sheet2 in that xxxxxxx portion. The name in the
parentheses (yyyyyyy) is the worksheet name you can see on the worksheet tab in
excel.

And I changed the name of your Sub, too. I don't like to use names that are
reserved words.
 
Thanks Dave
It works fine now....
-----Original Message-----
I'm not sure why you're getting the error--but I don't think it's the offset.

Here's my version (just slightly formatted):

Option Explicit
Sub MyClearContents()
Sheet2.Range("A65536").End(xlUp).Offset(1, 0).Value _
= Sheet1.Range("G63").Value
Sheet1.Range("G2:G62").ClearContents
End Sub

I changed the "sending" cell to point at sheet1. And I added Sheet1 to the
front of the .clearcontents line.

Are you getting a "Run time error - 424, Object required"? error?

If yes, then maybe it's because you don't have the worksheets that you refer to
in your code.

You're using the codenames within your code. When you're in the VBE, you can
look at the project explorer to verify the codename.

There's a section called "microsoft excel objects". Under that, you'll see the
worksheets:
xxxxxxx (yyyyyy)

You have to have sheet1 and sheet2 in that xxxxxxx portion. The name in the
parentheses (yyyyyyy) is the worksheet name you can see on the worksheet tab in
excel.

And I changed the name of your Sub, too. I don't like to use names that are
reserved words.




--

Dave Peterson
(e-mail address removed)
.
Click to expand...
 
Back
Top