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