Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Answers to questions posing more questions in a workbook | Excel Worksheet Functions | |||
View Questions and Answer to questions I created | Excel Discussion (Misc queries) | |||
HLOOKUP() explanation | Excel Worksheet Functions | |||
Explanation of when & how to use ( ) { } : ; , ! etc? | New Users to Excel | |||
Formula Explanation Please | Excel Discussion (Misc queries) |