Tombies February 2016

Select and clear contents of an unspecified cell range in newly copy and pasted worksheet

I am trying to achieve the following:

  • I have a VBA button that when clicked, copies the active worksheet and pastes it into a new worksheet (I have this function already working perfectly - see code below).

  • I then want the newly pasted worksheet to find two specified cells, which contain the texts ("bus start") and ("bus finish") and clear all the contents in the cells that are in between the range of the two specified cells.

The rows are dynamic and will change over time as rows get added and deleted, hence why I cannot define a fixed range. Columns however, will not be deleted or added and therefore will be fixed.

For reference, here is my copy and paste code:

Private Sub WkCapBtn_Click()

Dim Ws1 As Worksheet

Set Ws1 = ActiveSheet

    Ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
        ActiveSheet.Range("C3").Value = DateAdd("d", 7, ActiveSheet.Range("C3"))
            ActiveSheet.Name = Format(ActiveSheet.Range("A1").Value, ("dd-mmm-yy"))

End Sub

My initial thinking was to use a .Find function to locate the specified cells and then use a .Offset to select the cells below and above. Where I am stuck though is actually trying to define the range between the specified cells.

If any additional information is needed, please let me know. Thank you in advance!

Answers


Kathara February 2016

I corrected your code a little for the beginning:

Static WsCopy As Worksheet
Static Ws1 As Worksheet

Private Sub WkCapBtn_Click()

    Set Ws1 = ThisWorkbook.Sheets("ORIGINALSHEETNAME")

    Set WSCopy = Ws1.Copy After:=(ThisWorkbook.Sheets(Sheets.Count))
    With WSCopy
        .Name = Format(.Range("A1").Value, ("dd-mmm-yy"))
        .Range("C3").Value = DateAdd("d", 7, .Range("C3"))
    End With
End Sub

For your other sub you could set a variable for the cells containing either "bus start" or "bus finish". Can you try the following code and tell me if it worked?

Private Sub DelBusRange()

    Dim BusStart As Range
    Dim BusFinish As Range

    With WsCopy
        Set BusStart = .Find(What:="Bus start", _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlPart, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)

        Set BusFinish = .Find(What:="Bus finish", _
                         After:=.Cells(.Cells.Count), _
                         LookIn:=xlValues, _
                         LookAt:=xlPart, _
                         SearchOrder:=xlByRows, _
                         SearchDirection:=xlNext, _
                         MatchCase:=False)

        .Range(BusStart, BusFinish).clear
    End With
End Sub


Tombies February 2016

Chris Neilson's code did the trick once I changed it around.

Private Sub WkCapBtn_Click()

Dim Ws1 As Worksheet
Dim FindBus1 As String
Dim FindBus2 As String
Dim BusMatch1 As Range
Dim BusMatch2 As Range

Set Ws1 = ActiveSheet

    Ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
        ActiveSheet.Range("C3").Value = DateAdd("d", 7, ActiveSheet.Range("C3"))
            ActiveSheet.Name = Format(ActiveSheet.Range("A1").Value, ("dd-mmm-yy"))

        FindBus1 = "Bus Start"

        FindBus2 = "Bus Finish"
        Set BusMatch1 = ActiveSheet.Cells.Find(FindBus1)
        Set BusMatch2 = ActiveSheet.Cells.Find(FindBus2)

        ActiveSheet.Range(BusMatch1.Offset(3, 1), BusMatch2.Offset(-2, 1)).ClearContents


End Sub

Post Status

Asked in February 2016
Viewed 3,113 times
Voted 12
Answered 2 times

Search




Leave an answer