Please write me a VBA module that will do the following. Clear all contents of worksheet…
- 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
- 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 SubTo use this module, follow these steps:
- Press Alt+F11 to open the VBA Editor.
- Insert a new module by clicking on "Insert" > "Module".
- Copy and paste the code into the newly created module.
- Modify the worksheet names ("BOOKING" and "SsBOOKonly") if necessary.
- Close the VBA Editor.
- Run the macro by pressing Alt+F8, selecting "CopyDataAndCalculate", and clicking "Run".
This macro will perform the specified actions in the order you requested.
- 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”:
- 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 SubJust 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".
- 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
- 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 SubOnce 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.
- 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 - 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 iIn this updated code, the line
lastRow = wsTarget.Cells(wsTarget.Rows.Count, 1).End(xlUp).Rowspecifically 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.