Clear sheet data on changing sheet

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,047
Office Version
  1. 2016
Platform
  1. 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.
VBA Code:
Sheets("Sheet6").Cells.ClearFormats
Sheets("Sheet6").Cells.ClearContents
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
VBA Code:
GetElementByClassName(Sheets("Sheet10").Range("C4"))(Sheets("Sheet10").Range("D4")).innerText
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
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
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Maybe:
VBA Code:
Sheets(Sheets("Sheet5").Range("A1").Value.Range("A2")).Resize(i, 1) = w
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,047
Office Version
  1. 2016
Platform
  1. Windows
Sorry Mumps, but i'm getting a bit confused now, there are three parts that needed changing. The last bit of code you sent, I think is for Part 3 and not for Part 2 of the code. I have numbered the Parts

I have listed the code below and commented out any part that is either resolved or not an issue.
VBA Code:
'############################ Changes from here #######################
            '''clear sheet first and format
'Sheets(Sheets("Sheet5").Range("A1").Value).Cells.ClearContents ' Mumps code        ###### PART 1 #######
'Sheets(Sheets("Sheet5").Range("A1").Value).Cells.ClearFormats 'Mumps code          ###### PART 1 #######

  Sheet6.Range("A1").Value = "Your data has been pasted" 'I AM STUCK HERE, cant test the last bit of code until this is done #### PART 2 ####
               'On Error Resume Next
               ' ReDim w(1 To cnt + 1, 1 To 1)
               'For i = 0 To cnt
              '  w(i + 1, 1) = Replace(Split(Str, Chr(10))(i), Chr(13), "")
              ' Next i
''''' Sheet6.Range("A2").Resize(i, 1) = w ' My old code, Replaced with Munps code
'Sheets(Sheets("Sheet5").Range("A1").Value.Range("A2")).Resize(i, 1) = w 'Mumps new code  ###### PART 3 #######
'############################ Changes end  here #######################

This selects Sheet6 Range A1 and then place in the following TITLE "Your data has been pasted"
I need to reference both aspect of the code from a sheet, as the SHEET and TITLE will change. lets say sheet 5 Cells A1 and A2 for the title
VBA Code:
 Sheet6.Range("A1").Value = "Your data has been pasted"
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,047
Office Version
  1. 2016
Platform
  1. Windows
Look like I have done Part 2 of the code

VBA Code:
Sheets(Sheets("Sheet5").Range("A1").Value).Range("A1").Value = Sheets("Sheet5").Range("A2").Value
 

Watch MrExcel Video

Forum statistics

Threads
1,127,623
Messages
5,625,943
Members
416,143
Latest member
JoyceMB

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top