Sharid
Well-known Member
- Joined
- Apr 22, 2007
- Messages
- 1,064
- Office Version
- 2016
- Platform
- Windows
I am trying to clear data and format a sheet. Currently these line of code works, however they are only set to clear Sheet6. (This code is part of a much larger code.) The rest works fine this bit relates to clearing the sheet.
My problem is I have a clear button on several parts of a userform and have to keep using the same code over and over again, making the document large. I want to reduce the document size. Therefore each command button will have a desired sheet to clear/format in it and the code will be called up as a Module. In the past have been able to do this successfully with other codes and was trying to apply the same principle here, however I have been struggling to work it out.
Here is how I have done it in the past
In the above example, Sheet10,C4 + D4 content will change on click of a button, when the code is run it will get the data from Sheet 10 C4 + D4 and use it in the code. Therefore I do not have to keep writing different variations of the code for minor changes, JUST the content of Sheet10 C4 + D4 has changes and that can be easily added to a sheet reference.
The buttons will look like this, Sheet names will change, however the bulk of the code in the module will remain the same.
Button1
Button2 and so on.
I have tried the following but nothing seems to work
Hope this makes sence
VBA Code:
Sheets("Sheet6").Cells.ClearFormats
Sheets("Sheet6").Cells.ClearContents
Here is how I have done it in the past
VBA Code:
GetElementByClassName(Sheets("Sheet10").Range("C4"))(Sheets("Sheet10").Range("D4")).innerText
The buttons will look like this, Sheet names will change, however the bulk of the code in the module will remain the same.
Button1
VBA Code:
Command Button1_Click ()
Sheet5.Range("A1").Value = "Sheet10" ' The text "Sheet10" will be added to Sheet5 A1
Application.Run "Module1.CopyPasteClear" ' Code is written once and called as a module and will get data of Sheet to clear from Sheet5 A1
End Sub
Button2 and so on.
VBA Code:
Command Button2_Click ()
Sheet5.Range("A1").Value = "Sheet7" ' The text "Sheet7" will be added to Sheet5 A1
Application.Run "Module1.CopyPasteClear" ' Code is written once and called as a module and will get data of Sheet to clear from Sheet5 A1
End Sub
I have tried the following but nothing seems to work
VBA Code:
ThisWorkbook.Sheets("Sheet5").Range("A1").Cells.ClearContents
ThisWorkbook.Sheets("Sheet5").Range("A1").Cells.ClearFormats
VBA Code:
Sheets("Sheet5").Range("A1").Cells.ClearContents
Sheets("Sheet5").Range("A1").Cells.ClearFormats
VBA Code:
Sheets("Sheet5").Range("a1").Value.Replace.Cells.ClearFormats
Sheets("Sheet5").Range("a1").Value.Replace.Cells.ClearContents
Hope this makes sence