Clear sheet data on changing sheet

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,064
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Why not pass the name of the sheet you want to clear into the sub that does the clearing?
 
Upvote 0
Norie

Please explain with an example, if possible as my vba is limited so I am not sure what your are talking about.

Please keep in mind that this is small part of a larger code of copy paste and clear

VBA Code:
   Dim objdataobject As MSForms.DataObject
    Set objdataobject = New MSForms.DataObject
    Dim Str As String, a, i As Long, msg As String
    Dim cnt As Integer
    Dim w()

    If Sheet5.Range("J5").Value = "False" Then

       PasteMessage.show 'using userforms as messages
    Else
        objdataobject.GetFromClipboard

        If Me.PasteTextBox.Value = "" Then
            On Error Resume Next
            Me.PasteTextBox.Value = objdataobject.GetText

            Str = Replace(PasteTextBox.Value, Chr(13), "")
            a = Chr(10)
            cnt = UBound(Split(Str, a))
            If Len(Str) < 5 Then
               NothingToPaste.show 'using userforms as messages

            Else
'############################ Changes from here #######################
            '''clear sheet6 first and format
            Sheets("Sheet6").Cells.ClearFormats
            Sheets("Sheet6").Cells.ClearContents

            Sheet6.Range("A1").Value = "Your data has been pasted" 'TITLE Sheet6 A1
                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
'############################ Changes end  here #######################
         PasteTextBox.Value = ""
           PasteSucess.show 'using userforms as messages
        End If
        End If
        End If

   If Sheet5.Range("J5").Value = "True" Then
   Sheet5.Range("J5").Value = "False"
   End If
 
Upvote 0
Still stuck on this one I have tried may variations nothing seams to work, any reference to a work sheet will need to come off sheet5, that includes this block of code as currently it only refers to sheet6

VBA Code:
'############################ Changes from here #######################
            '''clear sheet6 first and format
            Sheets("Sheet6").Cells.ClearFormats
            Sheets("Sheet6").Cells.ClearContents

            Sheet6.Range("A1").Value = "Your data has been pasted" 'TITLE Sheet6 A1
                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
'############################ Changes end  here #######################
 
Upvote 0
To be honest, I'm not really following what your final goal is. However, if you want to use the value of A1 in Sheet5 as the sheet name to be referenced, then maybe this will work:
VBA Code:
Sheets(Sheets("Sheet5").Range("A1").Value).Cells.ClearContents
Sheets(Sheets("Sheet5").Range("A1").Value).Cells.ClearFormats
 
Upvote 0
Solution
Mumps

This is super, it is what I am after. Would the process be the same for all references to sheet6 here, with slight changes to the range

VBA Code:
'############################ Changes from here #######################
            '''clear sheet6 first and format
           ''''Sheets("Sheet6").Cells.ClearFormats
           '''''Sheets("Sheet6").Cells.ClearContents
Sheets(Sheets("Sheet5").Range("A1").Value).Cells.ClearContents ' Mumps code
Sheets(Sheets("Sheet5").Range("A1").Value).Cells.ClearFormats 'Mumps code

  Sheet6.Range("A1").Value = "Your data has been pasted" ' For here and the title

                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 ' for here as well
'############################ Changes end  here #######################

I have a User Form, with about 40+ places a copy/paste process can happen, rather that have the code 40+ times I want to reff the changes from a sheet so the bulk of the code is used once and the changes can come from a sheet, this will keep the project a smaller file and not work as slow on some user slow pc's
 
Upvote 0
I'm not sure what you mean but the code can be used to reference any sheet named in A1.
 
Upvote 0
Mumps your code works fine,

I also need it for these two rows in the code, which are above. But not sure how to write it.
This selects Sheet6 Range A1 and then place in the following title "Your data has been pasted"

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

Also this line of code
VBA Code:
 Sheet6.Range("A2").Resize(i, 1) = w

Currently I am using this, but not sure if it is going to work as I am stuck on the above code
VBA Code:
 Sheets(Sheets("Sheet5").Range("A1").Value).Resize(i, 1) = w
 
Upvote 0
Sheets(Sheets("Sheet5").Range("A1").Value).Resize(i, 1) = w
This should work if
VBA Code:
Sheets("Sheet5").Range("A1")
contains the name of the sheet where "w" is assigned to A1.
 
Upvote 0
Thanks for that,

However I am still stuck on this bit
VBA Code:
 Sheet6.Range("A1").Value = "Your data has been pasted"
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 sheets and titles will change. lets say sheet 5 Cells A1 and A2 for the title
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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