Clearing cell contents automatically
 

Not exactly one click, but in the with a single cell selected, do
Edit=goto=special and select Constants and Numbers, then OK, then delete.

If this results in too much being deleted, you can select a more appropriate
range and do it.

Assumes manual inputs are numbers. If text is included, then don't restrict
the selection to numbers.

I am sure the transfer could be easily coded in a macro, but without knowing
the layout of the sheet and what you want to transfer and where, it would be
hard to provide anything specific.

Sub TransData()
dim i as long, mysource as variant, mydest as variant
dim myclear as variant
mysource = Array("A1","B9","C11","G13")
myDest = Array("M6","R13","Z26","B4")

for i = lbound(mysource) to ubound(mysource)
Range(mydest(i)).Value = Range(mysource(i)).Value
Range(mysource(i)).ClearContents
Next

' option - clear manual inputs
myclear = Array(A3,B5:B30,R11,M31)
for i = lbound(myclear) to ubound(myclear)
Range(myclear(i)).ClearContents
Next
' or
' Range("A3,B5:B30,R11,M31").ClearContents

end Sub


so fill in the appropriate cells in each array. mysource and mydest must
have the same number of elements.

--
Regards,
Tom Ogilvy


"Jun" wrote in message
...
I have an Excel file with three worksheets which makes
cross references to each other to get certain values. It
is basically a simple weekly finace sheet.

I was wondering if there was an easy one click way of
deleting the contents of only those cells which require
manual input each week.

I am also looking to transfer the current week's figures
into last week's figures cell ready for use the following
week.

Any hints and tips would be gratefully received.
Many thanks.

Clearing cell contents automatically
 

Thank you for your advice. I will try it out. Is there a
way of creating a button on the worksheet which can then
execute the definition I create for the cells I want the
contents deleted?

I have created a macro to do it but am not sure if that
can then be attached to a button.

Thanks again.
Jun

-----Original Message-----
Not exactly one click, but in the with a single cell
selected, do
Edit=goto=special and select Constants and Numbers,
then OK, then delete.

If this results in too much being deleted, you can
select a more appropriate
range and do it.

Assumes manual inputs are numbers. If text is included,
then don't restrict
the selection to numbers.

I am sure the transfer could be easily coded in a macro,
but without knowing
the layout of the sheet and what you want to transfer
and where, it would be
hard to provide anything specific.

Sub TransData()
dim i as long, mysource as variant, mydest as variant
dim myclear as variant
mysource = Array("A1","B9","C11","G13")
myDest = Array("M6","R13","Z26","B4")

for i = lbound(mysource) to ubound(mysource)
Range(mydest(i)).Value = Range(mysource(i)).Value
Range(mysource(i)).ClearContents
Next

' option - clear manual inputs
myclear = Array(A3,B5:B30,R11,M31)
for i = lbound(myclear) to ubound(myclear)
Range(myclear(i)).ClearContents
Next
' or
' Range("A3,B5:B30,R11,M31").ClearContents

end Sub


so fill in the appropriate cells in each array.
mysource and mydest must
have the same number of elements.

--
Regards,
Tom Ogilvy


"Jun" wrote in message
...
I have an Excel file with three worksheets which makes
cross references to each other to get certain values.
It
is basically a simple weekly finace sheet.

I was wondering if there was an easy one click way of
deleting the contents of only those cells which require
manual input each week.

I am also looking to transfer the current week's
figures
into last week's figures cell ready for use the
following
week.

Any hints and tips would be gratefully received.
Many thanks.


.

Clearing cell contents automatically
 

if the button is from the control toolbox toolbar, then you call the macro
from the Click event of the commandbutton.

If it is from the forms toolbar, you can assign the macro to the button.
When you place the button on the sheet, you should be prompted to assign a
macro.
--
Regards,
Tom Ogilvy

Jun wrote in message
...
Thank you for your advice. I will try it out. Is there a
way of creating a button on the worksheet which can then
execute the definition I create for the cells I want the
contents deleted?

I have created a macro to do it but am not sure if that
can then be attached to a button.

Thanks again.
Jun

-----Original Message-----
Not exactly one click, but in the with a single cell
selected, do
Edit=goto=special and select Constants and Numbers,
then OK, then delete.

If this results in too much being deleted, you can
select a more appropriate
range and do it.

Assumes manual inputs are numbers. If text is included,
then don't restrict
the selection to numbers.

I am sure the transfer could be easily coded in a macro,
but without knowing
the layout of the sheet and what you want to transfer
and where, it would be
hard to provide anything specific.

Sub TransData()
dim i as long, mysource as variant, mydest as variant
dim myclear as variant
mysource = Array("A1","B9","C11","G13")
myDest = Array("M6","R13","Z26","B4")

for i = lbound(mysource) to ubound(mysource)
Range(mydest(i)).Value = Range(mysource(i)).Value
Range(mysource(i)).ClearContents
Next

' option - clear manual inputs
myclear = Array(A3,B5:B30,R11,M31)
for i = lbound(myclear) to ubound(myclear)
Range(myclear(i)).ClearContents
Next
' or
' Range("A3,B5:B30,R11,M31").ClearContents

end Sub


so fill in the appropriate cells in each array.
mysource and mydest must
have the same number of elements.

--
Regards,
Tom Ogilvy


"Jun" wrote in message
...
I have an Excel file with three worksheets which makes
cross references to each other to get certain values.
It
is basically a simple weekly finace sheet.

I was wondering if there was an easy one click way of
deleting the contents of only those cells which require
manual input each week.

I am also looking to transfer the current week's
figures
into last week's figures cell ready for use the
following
week.

Any hints and tips would be gratefully received.
Many thanks.


.