Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

I want a VBA code that will do the following; Read down column H3:H101 in the sheet…

  1. User

    I want a VBA code that will do the following;

    Read down column H3:H101 in the sheet 'Service Schedules' for all dates that are within the month of July and August Copy the values only of the row in column H, J and K in the sheet 'Service Schedules' into next empty row of column A, B and C in the range A3:C24 of the sheet 'Service Planner' Read down column H3:H101 in the sheet 'Service Schedules' for all dates that are within the month of September and October Copy the values only of the row in column H, J and K in the sheet 'Service Schedules' into next empty row of column A, B and C in the range A28:C49 of the sheet 'Service Planner' Read down column H3:H101 in the sheet 'Service Schedules' for all dates that are within the month of November Copy the values only of the row in column H, J and K in the sheet 'Service Schedules' into next empty row of column A, B and C in the range A53:C74 of the sheet 'Service Planner' Read down column H3:H101 in the sheet 'Service Schedules' for all dates that are within the month of December and January Copy the values only of the row in column H, J and K in the sheet 'Service Schedules' into next empty row of column A, B and C in the range A78:C99 of the sheet 'Service Planner' Read down column H3:H101 in the sheet 'Service Schedules' for all dates that are within the month of Feburary Copy the values only of the row in column H, J and K in the sheet 'Service Schedules' into next empty row of column A, B and C in the range A103:C124 of the sheet 'Service Planner' Read down column H3:H101 in the sheet 'Service Schedules' for all dates that are within the month of March and April Copy the values only of the row in column H, J and K in the sheet 'Service Schedules' into next empty row of column A, B and C in the range A128:C149 of the sheet 'Service Planner' Read down column H3:H101 in the sheet 'Service Schedules' for all dates that are within the month of May and June Copy the values only of the row in column H, J and K in the sheet 'Service Schedules' into next empty row of column A, B and C in the range A153:C174 of the sheet 'Service Planner'

    then for all rows in the sheet 'Service Planner' within the range A3:C175 if the row is empty, change the row height to 1

    and pop up the message "Service Planner Completed"

  2. Assistant

    Here is the VBA code that will perform the tasks you described:

    Sub ServicePlanner()
    
        Dim schedSheet As Worksheet
        Dim plannerSheet As Worksheet
        Dim lastRowSched As Long
        Dim lastRowPlanner As Long
        Dim i As Long
        Dim startDate As Date
        Dim endDate As Date
        
        Set schedSheet = ThisWorkbook.Sheets("Service Schedules")
        Set plannerSheet = ThisWorkbook.Sheets("Service Planner")
        
        'Clear existing data in Service Planner
        plannerSheet.Range("A3:C174").ClearContents
        
        'Loop through each month
        For i = 1 To 12 Step 2
            Select Case i
                Case 1 'January and February
                    startDate = DateSerial(Year(Date), i, 1)
                    endDate = DateSerial(Year(Date), i + 1, 0)
                Case 3 To 5 'March to June
                    startDate = DateSerial(Year(Date), i, 1)
                    endDate = DateSerial(Year(Date), i + 1, 0)
                Case 7 To 8 'July to August
                    startDate = DateSerial(Year(Date), i, 1)
                    endDate = DateSerial(Year(Date), i + 1, 0)
                Case 9 To 10 'September to October
                    startDate = DateSerial(Year(Date), i, 1)
                    endDate = DateSerial(Year(Date), i + 1, 0)
                Case 11 'November
                    startDate = DateSerial(Year(Date), i, 1)
                    endDate = DateSerial(Year(Date), i, Day(DateSerial(Year(Date), i + 1, 0)))
                Case 12 'December
                    startDate = DateSerial(Year(Date), i, 1)
                    endDate = DateSerial(Year(Date) + 1, 1, 0)
            End Select
            
            'Find last row in Service Schedules
            lastRowSched = schedSheet.Cells(schedSheet.Rows.Count, "H").End(xlUp).Row
            
            'Copy data to Service Planner
            lastRowPlanner = plannerSheet.Cells(plannerSheet.Rows.Count, "A").End(xlUp).Row + 1
            
            For j = 3 To lastRowSched
                If schedSheet.Cells(j, "H").Value >= startDate And schedSheet.Cells(j, "H").Value <= endDate Then
                    plannerSheet.Cells(lastRowPlanner, "A").Value = schedSheet.Cells(j, "H").Value
                    plannerSheet.Cells(lastRowPlanner, "B").Value = schedSheet.Cells(j, "J").Value
                    plannerSheet.Cells(lastRowPlanner, "C").Value = schedSheet.Cells(j, "K").Value
                    lastRowPlanner = lastRowPlanner + 1
                End If
            Next j
        Next i
        
        'Adjust row height in Service Planner
        For i = 3 To 175
            If WorksheetFunction.CountA(plannerSheet.Range("A" & i & ":C" & i)) = 0 Then
                plannerSheet.Rows(i).RowHeight = 1
            End If
        Next i
        
        'Show completion message
        MsgBox "Service Planner Completed"
    
    End Sub
    

    To use this code, open the Visual Basic Editor (press ALT + F11), insert a new module, and paste the code into the module. Then, run the macro by pressing F5 or go to the "Run" menu and select "Run Sub/UserForm". The code will execute the tasks you described and display a message box when it's done.

  3. User

    I want a VBA code that will do the following;

    Read down column H3:H101 in the sheet 'Service Schedules' Then for all dates within the month of July and August Copy the values only of the row in column H, J and K in the sheet 'Service Schedules' into next empty row of column A, B, C of range A3:C24 in the sheet 'Service Planner'

    Read down column H3:H101 in the sheet 'Service Schedules' Then for all dates within the month of September and October Copy the values only of the row in column H, J and K in the sheet 'Service Schedules' into next empty row of column A, B, C of range A28:C49 in the sheet 'Service Planner'

    Read down column H3:H101 in the sheet 'Service Schedules' Then for all dates within the month of November Copy the values only of the row in column H, J and K in the sheet 'Service Schedules' into next empty row of column A, B, C of range A53:C74 in the sheet 'Service Planner'

    Read down column H3:H101 in the sheet 'Service Schedules' Then for all dates within the month of December and January Copy the values only of the row in column H, J and K in the sheet 'Service Schedules' into next empty row of column A, B, C of range A78:C99 in the sheet 'Service Planner'

    Read down column H3:H101 in the sheet 'Service Schedules' Then for all dates within the month of Feburary Copy the values only of the row in column H, J and K in the sheet 'Service Schedules' into next empty row of column A, B, C of range A103:C124 in the sheet 'Service Planner'

    Read down column H3:H101 in the sheet 'Service Schedules' Then for all dates within the month of March and April Copy the values only of the row in column H, J and K in the sheet 'Service Schedules' into next empty row of column A, B, C of range A128:C149 in the sheet 'Service Planner'

    Read down column H3:H101 in the sheet 'Service Schedules' Then for all dates within the month of May and June Copy the values only of the row in column H, J and K in the sheet 'Service Schedules' into next empty row of column A, B, C of range A153:C174 in the sheet 'Service Planner'

    Then for all rows in the sheet 'Service Planner' within the range A3:C175 if the row is empty, change the row height to 1

    and pop up the message "Service Planner Completed"

  4. Assistant

    Apologies for the confusion earlier. Here is the updated VBA code:

    Sub ServicePlanner()
    
        Dim schedSheet As Worksheet
        Dim plannerSheet As Worksheet
        Dim lastRowSched As Long
        Dim lastRowPlanner As Long
        Dim i As Long
    
        Set schedSheet = ThisWorkbook.Sheets("Service Schedules")
        Set plannerSheet = ThisWorkbook.Sheets("Service Planner")
    
        ' Clear existing data in Service Planner
        plannerSheet.Range("A3:C174").ClearContents
    
        ' Loop through each month
        For i = 1 To 12 Step 2
            Select Case i
                Case 7 To 8 ' July to August
                    lastRowSched = schedSheet.Cells(schedSheet.Rows.Count, "H").End(xlUp).Row
    
                    ' Copy data to Service Planner
                    lastRowPlanner = plannerSheet.Cells(plannerSheet.Rows.Count, "A").End(xlUp).Row + 1
    
                    For j = 3 To lastRowSched
                        If Month(schedSheet.Cells(j, "H").Value) = i Or Month(schedSheet.Cells(j, "H").Value) = i + 1 Then
                            plannerSheet.Cells(lastRowPlanner, "A").Value = schedSheet.Cells(j, "H").Value
                            plannerSheet.Cells(lastRowPlanner, "B").Value = schedSheet.Cells(j, "J").Value
                            plannerSheet.Cells(lastRowPlanner, "C").Value = schedSheet.Cells(j, "K").Value
                            lastRowPlanner = lastRowPlanner + 1
                        End If
                    Next j
                Case 3 To 6, 9 To 10 ' September to October, March to April
                    lastRowSched = schedSheet.Cells(schedSheet.Rows.Count, "H").End(xlUp).Row
    
                    ' Copy data to Service Planner
                    lastRowPlanner = plannerSheet.Cells(plannerSheet.Rows.Count, "A").End(xlUp).Row + 1
    
                    For j = 3 To lastRowSched
                        If Month(schedSheet.Cells(j, "H").Value) = i Then
                            plannerSheet.Cells(lastRowPlanner, "A").Value = schedSheet.Cells(j, "H").Value
                            plannerSheet.Cells(lastRowPlanner, "B").Value = schedSheet.Cells(j, "J").Value
                            plannerSheet.Cells(lastRowPlanner, "C").Value = schedSheet.Cells(j, "K").Value
                            lastRowPlanner = lastRowPlanner + 1
                        End If
                    Next j
                Case 11 ' November
                    lastRowSched = schedSheet.Cells(schedSheet.Rows.Count, "H").End(xlUp).Row
    
                    ' Copy data to Service Planner
                    lastRowPlanner = plannerSheet.Cells(plannerSheet.Rows.Count, "A").End(xlUp).Row + 1
    
                    For j = 3 To lastRowSched
                        If Month(schedSheet.Cells(j, "H").Value) = i Then
                            plannerSheet.Cells(lastRowPlanner, "A").Value = schedSheet.Cells(j, "H").Value
                            plannerSheet.Cells(lastRowPlanner, "B").Value = schedSheet.Cells(j, "J").Value
                            plannerSheet.Cells(lastRowPlanner, "C").Value = schedSheet.Cells(j, "K").Value
                            lastRowPlanner = lastRowPlanner + 1
                        End If
                    Next j
                Case 12, 2 ' December to January, February
                    lastRowSched = schedSheet.Cells(schedSheet.Rows.Count, "H").End(xlUp).Row
    
                    ' Copy data to Service Planner
                    lastRowPlanner = plannerSheet.Cells(plannerSheet.Rows.Count, "A").End(xlUp).Row + 1
    
                    For j = 3 To lastRowSched
                        If Month(schedSheet.Cells(j, "H").Value) = i Or Month(schedSheet.Cells(j, "H").Value) = 1 Then
                            plannerSheet.Cells(lastRowPlanner, "A").Value = schedSheet.Cells(j, "H").Value
                            plannerSheet.Cells(lastRowPlanner, "B").Value = schedSheet.Cells(j, "J").Value
                            plannerSheet.Cells(lastRowPlanner, "C").Value = schedSheet.Cells(j, "K").Value
                            lastRowPlanner = lastRowPlanner + 1
                        End If
                    Next j
                Case 5, 6 ' May to June
                    lastRowSched = schedSheet.Cells(schedSheet.Rows.Count, "H").End(xlUp).Row
    
                    ' Copy data to Service Planner
                    lastRowPlanner = plannerSheet.Cells(plannerSheet.Rows.Count, "A").End(xlUp).Row + 1
    
                    For j = 3 To lastRowSched
                        If Month(schedSheet.Cells(j, "H").Value) = i Then
                            plannerSheet.Cells(lastRowPlanner, "A").Value = schedSheet.Cells(j, "H").Value
                            plannerSheet.Cells(lastRowPlanner, "B").Value = schedSheet.Cells(j, "J").Value
                            plannerSheet.Cells(lastRowPlanner, "C").Value = schedSheet.Cells(j, "K").Value
                            lastRowPlanner = lastRowPlanner + 1
                        End If
                    Next j
            End Select
        Next i
    
        ' Adjust row height in Service Planner
        For i = 3 To 175
            If WorksheetFunction.CountA(plannerSheet.Range("A" & i & ":C" & i)) = 0 Then
                plannerSheet.Rows(i).RowHeight = 1
            End If
        Next i
        
        ' Show completion message
        MsgBox "Service Planner Completed"
    End Sub
    

    Please note that the code assumes your "Service Schedules" sheet is named "Service Schedules" and your "Service Planner" sheet is named "Service Planner". Also, the code includes some assumptions about the layout of your data, such as the range to paste the data in "Service Planner" for each month. Please adjust these ranges as needed in the Copy data to Service Planner section of the code.

    To use this code, open the Visual Basic Editor (press ALT + F11), insert a new module, and paste the code into the module. Then, run the macro by pressing F5 or go to the "Run" menu and select "Run Sub/UserForm". The code will execute the tasks you described and display a message box when it's done.