Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· openaiHugging Face Suzhen/CodeChat

Please write me a VBA module that will do the following. Clear all contents of worksheet…

  1. User

    Please write me a VBA module that will do the following.

    Clear all contents of worksheet SsBOOKonly Copy all values and format only of worksheet BOOKING to SsBOOKonly Then copy all formulas only in column H and I from worksheet BOOKING to column H and I in worksheet SsBOOKonly Then calculate the entire worksheet SsBOOKonly

  2. Assistant

    Sure! Here's a VBA module that will clear the contents of worksheet "SsBOOKonly", copy values and formats from worksheet "BOOKING" to "SsBOOKonly", copy formulas from column H and I in "BOOKING" to column H and I in "SsBOOKonly", and calculate the entire worksheet "SsBOOKonly":

    Sub CopyDataAndCalculate()
        Dim wsSource As Worksheet, wsTarget As Worksheet
        Dim rngSource As Range, rngTarget As Range
        
        ' Set the source and target worksheets
        Set wsSource = ThisWorkbook.Worksheets("BOOKING")
        Set wsTarget = ThisWorkbook.Worksheets("SsBOOKonly")
        
        ' Clear contents of target sheet
        wsTarget.Cells.Clear
        
        ' Copy values and formats from source to target
        Set rngSource = wsSource.UsedRange
        Set rngTarget = wsTarget.Range("A1").Resize(rngSource.Rows.Count, rngSource.Columns.Count)
        rngSource.Copy
        rngTarget.PasteSpecial xlPasteValues
        rngTarget.PasteSpecial xlPasteFormats
        
        ' Copy formulas from source to target (columns H and I)
        wsSource.Range("H:I").Copy
        wsTarget.Range("H:I").PasteSpecial xlPasteFormulas
        
        ' Calculate the target sheet
        wsTarget.Calculate
    End Sub
    

    To use this module, follow these steps:

    1. Press Alt+F11 to open the VBA Editor.
    2. Insert a new module by clicking on "Insert" > "Module".
    3. Copy and paste the code into the newly created module.
    4. Modify the worksheet names ("BOOKING" and "SsBOOKonly") if necessary.
    5. Close the VBA Editor.
    6. Run the macro by pressing Alt+F8, selecting "CopyDataAndCalculate", and clicking "Run".

    This macro will perform the specified actions in the order you requested.

  3. User

    Can you rite it to copy formulas and formats from column H and I in “BOOKING” to column H and I in “SsBOOKonly”, and calculate the entire worksheet “SsBOOKonly”:

  4. Assistant

    Certainly! Here's the modified VBA code that copies formulas and formats from column H and I in "BOOKING" to column H and I in "SsBOOKonly" and calculates the entire worksheet "SsBOOKonly":

    Sub CopyFormulasAndCalculate()
        Dim wsSource As Worksheet, wsTarget As Worksheet
        Dim rngSource As Range, rngTarget As Range
        
        ' Set the source and target worksheets
        Set wsSource = ThisWorkbook.Worksheets("BOOKING")
        Set wsTarget = ThisWorkbook.Worksheets("SsBOOKonly")
        
        ' Clear contents of target sheet
        wsTarget.Cells.Clear
        
        ' Copy values and formats from source to target
        Set rngSource = wsSource.UsedRange
        Set rngTarget = wsTarget.Range("A1").Resize(rngSource.Rows.Count, rngSource.Columns.Count)
        rngSource.Copy
        rngTarget.PasteSpecial xlPasteValuesAndNumberFormats
        
        ' Copy formulas from source to target (columns H and I)
        wsSource.Range("H:I").Copy
        wsTarget.Range("H:I").PasteSpecial xlPasteFormulasAndNumberFormats
        
        ' Calculate the target sheet
        wsTarget.Calculate
    End Sub
    

    Just like before, you can follow the same steps to add this code to a VBA module and run the macro. This updated code now copies both formulas and formats from column H and I in "BOOKING" to column H and I in "SsBOOKonly" and calculates the entire worksheet "SsBOOKonly".

  5. User

    I forgot to add a condition to the code. After the values and formats have been pasted from BOOKING to SsBOOKonly, it must delete in SsBOOKonly, all rows that contain the value 'School Bookin' in column B, then paste the foormulas and formats from column H & I in BOOKING to H & I in SsBOOKonly

  6. Assistant

    Apologies for the confusion. Here's the updated VBA code that includes the condition to delete rows in "SsBOOKonly" containing the value 'School Bookin' in column B after copying values and formats from "BOOKING" to "SsBOOKonly". It will then proceed to paste the formulas and formats from column H and I in "BOOKING" to column H and I in "SsBOOKonly" and calculate the entire worksheet "SsBOOKonly":

    Sub CopyFormulasAndCalculate()
        Dim wsSource As Worksheet, wsTarget As Worksheet
        Dim rngSource As Range, rngTarget As Range, rngDelete As Range, cell As Range
        
        ' Set the source and target worksheets
        Set wsSource = ThisWorkbook.Worksheets("BOOKING")
        Set wsTarget = ThisWorkbook.Worksheets("SsBOOKonly")
        
        ' Clear contents of target sheet
        wsTarget.Cells.Clear
        
        ' Copy values and formats from source to target
        Set rngSource = wsSource.UsedRange
        Set rngTarget = wsTarget.Range("A1").Resize(rngSource.Rows.Count, rngSource.Columns.Count)
        rngSource.Copy
        rngTarget.PasteSpecial xlPasteValuesAndNumberFormats
        
        ' Delete rows with 'School Bookin' in column B
        Set rngDelete = Nothing ' Reset delete range
        For Each cell In wsTarget.Range("B:B")
            If cell.Value = "School Bookin" Then
                If rngDelete Is Nothing Then
                    Set rngDelete = cell.EntireRow
                Else
                    Set rngDelete = Union(rngDelete, cell.EntireRow)
                End If
            End If
        Next cell
        If Not rngDelete Is Nothing Then rngDelete.Delete
        
        ' Copy formulas from source to target (columns H and I)
        wsSource.Range("H:I").Copy
        wsTarget.Range("H:I").PasteSpecial xlPasteFormulasAndNumberFormats
        
        ' Calculate the target sheet
        wsTarget.Calculate
    End Sub
    

    Once again, follow the same steps to add this code to a VBA module, run the macro, and it will carry out the updated requirements - copying values and formats, deleting rows with 'School Bookin' in column B, and then copying formulas and formats to column H and I in "SsBOOKonly" while also performing calculations for the target sheet.

  7. User

    I am running the following event from a module and need the code to run only on 'wsTarget' as I have shown in this line 'lastRow = wsTarget.Cells(Rows.Count, 1).End(xlUp).row' Can you help me to place the 'wsTarget' where it needs to be in the event below.

    Dim lastRow As Long Dim currentGroupStartRow As Long Dim currentDate As Date Dim latestTime As Date Dim maxTimeRow As Long

    lastRow = wsTarget.Cells(Rows.Count, 1).End(xlUp).row
    
    ' Start from the second row
    currentGroupStartRow = 2
    
    ' Loop through all rows
    For i = 2 To lastRow
        ' Check if the current date changes
        'If IsEmpty(Cells(i, 1).Value) Then Exit For ' Stop if empty cell is encountered
                If (Cells(i, 1).Value) = "" Then Exit Sub ' Stop if empty cell is encountered
        If Cells(i, 1).Value <> currentDate Then
            ' If it changes, calculate the latest time in the previous group
            latestTime = Cells(currentGroupStartRow, 5).Value
            maxTimeRow = currentGroupStartRow
            
            For j = currentGroupStartRow + 1 To i - 1
                If Cells(j, 5).Value > latestTime Then
                    latestTime = Cells(j, 5).Value
                    maxTimeRow = j
                End If
            Next j
            
            ' Append "Close" to column V in the row with the latest time
            If (Cells(i, 1).Value) = "" Then Exit Sub ' Stop if empty cell is encountered
            If maxTimeRow <= lastRow Then ' Check if maxTimeRow is within bounds
                Cells(maxTimeRow, 22).Value = "Close"
            End If
            
            ' Update variables for the next group
            currentDate = Cells(i, 1).Value
            currentGroupStartRow = i
        End If
    Next i
    
  8. Assistant

    Certainly! Based on the provided code, you can specify the worksheet "wsTarget" in the lines where it is necessary. Here's the updated code with "wsTarget" specified in the required line:

    Dim lastRow As Long
    Dim currentGroupStartRow As Long
    Dim currentDate As Date
    Dim latestTime As Date
    Dim maxTimeRow As Long
    
    lastRow = wsTarget.Cells(wsTarget.Rows.Count, 1).End(xlUp).Row ' Update the line with "wsTarget"
    
    ' Start from the second row
    currentGroupStartRow = 2
    
    ' Loop through all rows
    For i = 2 To lastRow
        ' Check if the current date changes
        'If IsEmpty(wsTarget.Cells(i, 1).Value) Then Exit For ' Stop if empty cell is encountered
        If wsTarget.Cells(i, 1).Value = "" Then Exit Sub ' Stop if empty cell is encountered
        If wsTarget.Cells(i, 1).Value <> currentDate Then
            ' If it changes, calculate the latest time in the previous group
            latestTime = wsTarget.Cells(currentGroupStartRow, 5).Value
            maxTimeRow = currentGroupStartRow
            
            For j = currentGroupStartRow + 1 To i - 1
                If wsTarget.Cells(j, 5).Value > latestTime Then
                    latestTime = wsTarget.Cells(j, 5).Value
                    maxTimeRow = j
                End If
            Next j
            
            ' Append "Close" to column V in the row with the latest time
            If wsTarget.Cells(i, 1).Value = "" Then Exit Sub ' Stop if empty cell is encountered
            If maxTimeRow <= lastRow Then ' Check if maxTimeRow is within bounds
                wsTarget.Cells(maxTimeRow, 22).Value = "Close"
            End If
            
            ' Update variables for the next group
            currentDate = wsTarget.Cells(i, 1).Value
            currentGroupStartRow = i
        End If
    Next i
    

    In this updated code, the line lastRow = wsTarget.Cells(wsTarget.Rows.Count, 1).End(xlUp).Row specifically refers to worksheet "wsTarget" when determining the last row. The rest of the code remains the same. Please ensure that "wsTarget" is correctly defined and initialized before running this event.