Copy and clear problem

S

sminky

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
 
C

Chip Pearson

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
 
F

Frank Kabel

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

Bob Phillips

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)
 
Top