Copy and clear problem
 

Hi

I am using Excel 97 (VBA)and have a problem. Basically what I want to
do is copy two cells, clear the whole sheet and then paste the
contents of the cells back onto the sheet. The problem I am having is
that as the contents of the cells are derived from a calculation on
the sheet, when it attempts to paste the contents back onto the sheet
there is nothing to paste (don't know why). I am attempting to do
this using a macro.

If you understand my problem and think you can help, please post the
relevant code.

Thanks

Copy and clear problem
 

I would store the values of the cells in two variables, clear the
sheet, and then insert the contents of the variables back in to
the sheet.

Dim V1 As Variant
Dim V2 As Variant
V1 = Range("A1").Value '<< Change range
V2 = Range("A2").Value ' << Change range
Cells.Clear
Range("A1").Value = V1 ' << Change range
Range("A2").Value = V2 ' << Change range


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"sminky" wrote in message
m...
Hi

I am using Excel 97 (VBA)and have a problem. Basically what I
want to
do is copy two cells, clear the whole sheet and then paste the
contents of the cells back onto the sheet. The problem I am
having is
that as the contents of the cells are derived from a
calculation on
the sheet, when it attempts to paste the contents back onto the
sheet
there is nothing to paste (don't know why). I am attempting to
do
this using a macro.

If you understand my problem and think you can help, please
post the
relevant code.

Thanks

Copy and clear problem
 

Hi
try doing this manually while recording a macro. Also use 'Edit - Paste
Special - Values' to paste your content

--
Regards
Frank Kabel
Frankfurt, Germany


sminky wrote:
Hi

I am using Excel 97 (VBA)and have a problem. Basically what I want
to
do is copy two cells, clear the whole sheet and then paste the
contents of the cells back onto the sheet. The problem I am having
is
that as the contents of the cells are derived from a calculation on
the sheet, when it attempts to paste the contents back onto the sheet
there is nothing to paste (don't know why). I am attempting to do
this using a macro.

If you understand my problem and think you can help, please post the
relevant code.

Thanks

Copy and clear problem
 

Try this

Val1 = Range("C11").Value
Val2 = Range("D13").Value
Cells.ClearContents
Range("C11").Value = Val1
Range("D13").Value = Val2

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"sminky" wrote in message
m...
Hi

I am using Excel 97 (VBA)and have a problem. Basically what I want to
do is copy two cells, clear the whole sheet and then paste the
contents of the cells back onto the sheet. The problem I am having is
that as the contents of the cells are derived from a calculation on
the sheet, when it attempts to paste the contents back onto the sheet
there is nothing to paste (don't know why). I am attempting to do
this using a macro.

If you understand my problem and think you can help, please post the
relevant code.

Thanks