2 more questions explanation
 

I need to be more precise:

I have a 3 sheets workbook: 1 Main, and the other 2 are to input the data.
At the end of the day, when all the data is input in the 2 worksheets, a
button is pressed and this button will start a macro which will select all
data, copy it, open the sheet calle main, paste the cut data in the first
available empty place and then delete the data in the workbook. Only 1 cell
per row in this worksheet has a formula (i.e. =H2 - E2). When the macro
deletes all the data in the input sheet, it also deletes the formula... how
can I prevent it from happening? is it possible to create a macro or a code
that will restore the formula in the given cell(s)?

2 more questions explanation
 

How are you deleteing the data?
You can use CleaContents to delete the data. Before doing this check if
the cell HasFormula, if not then ClearContents.

Please see code below, which will clear the data in all worksheets in
workbook except the one, name of which you enter below in place of
Main_Sheet_Name_Here. But it will keep all formulae.

Dim c, sh
For Each sh In ThisWorkbook.Sheets
If sh.Name < "Main_Sheet_Name_Here" Then
For Each c In sh.UsedRange
If Not c.HasFormula Then c.ClearContents
Next c
End If
Next sh

Sharad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

2 more questions explanation
 

Hello Sharad, thanks for your reply. I have made a button (linked to a
recorded macro) that will select all rows and columns of the active sheet
(yes, all 65536 rows even though the data is written in just 1000 of so of
them) and then all data is copied into the main sheet and then I just
recorded the Delete button to clear the data. Now, as I am not a wiz.... how
am I suppose to use the code you sent me? do I have to link it to a button
or so?
thanks again and merry xmas
"Sharad" wrote in message
...
How are you deleteing the data?
You can use CleaContents to delete the data. Before doing this check if
the cell HasFormula, if not then ClearContents.

Please see code below, which will clear the data in all worksheets in
workbook except the one, name of which you enter below in place of
Main_Sheet_Name_Here. But it will keep all formulae.

Dim c, sh
For Each sh In ThisWorkbook.Sheets
If sh.Name < "Main_Sheet_Name_Here" Then
For Each c In sh.UsedRange
If Not c.HasFormula Then c.ClearContents
Next c
End If
Next sh

Sharad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!