Skip to content
The Chat Archive
gpt-3.5-turbo-0301· OpenAI WildChat-4.8M (AI2)

In excel, using a form button in Sheet Job Request, I want the Sheet Job Request to…

  1. User

    In excel, using a form button in Sheet Job Request, I want the Sheet Job Request to first calculate, then 4 seconds later copy the last 10 cells with values fom Column J of the Sheet named in I2 of the sheet Job Request,and paste the copied values into Column E starting from E5 of the Sheet Request Sheet.

  2. Assistant

    To achieve this, you can use the following VBA code:

    1. Right-click on the form button in Sheet Job Request, select "Assign Macro", and create a new macro called "CopyLast10Values".

    2. In the VBA editor, paste the following code:

    Sub CopyLast10Values()
        'Calculate before copying
        Application.Calculate
        
        'Wait 4 seconds
        Application.Wait Now + TimeValue("0:00:04")
        
        'Copy last 10 values from column J of the sheet named in cell I2
        Dim srcSheet As Worksheet
        Dim dstSheet As Worksheet
        Set srcSheet = Sheets(Range("I2").Value)
        Set dstSheet = Sheets("Request Sheet")
        Dim lastRow As Long
        lastRow = srcSheet.Cells(srcSheet.Rows.Count, "J").End(xlUp).Row
        srcSheet.Range("J" & lastRow - 9 & ":J" & lastRow).Copy
        
        'Paste copied values into column E starting from E5 of the Request Sheet
        dstSheet.Range("E5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End Sub
    
    1. Save the macro and close the VBA editor.

    Now, when you click the form button in Sheet Job Request, the macro will calculate the workbook, wait 4 seconds, and then copy the last 10 values from Column J of the sheet named in I2. These values will be pasted into Column E starting from E5 of the Request Sheet.

  3. User

    i am getting a subscript out of range

  4. Assistant

    The "Subscript out of Range" error occurs when you try to reference a non-existing object in your macro. There might be several reasons why you are getting this error message, but here are some potential issues and solutions:

    1. Sheet names: Make sure that the sheet name you are trying to reference is spelled correctly. If the sheet name contains spaces or special characters, make sure that you are enclosing the name in single quotes.

    2. Sheet index: If you are trying to reference a sheet using its index number (e.g., Sheets(1)), make sure that the index corresponds to the sheet you are trying to reference. Sheet index numbers start from 1, so if you have deleted or added sheets, the index numbers might have shifted.

    3. Range references: Make sure that the range references in your macro are correct. If you are trying to reference a range that is outside the bounds of your worksheet, you will get a subscript out of range error. Double-check the row and column numbers in your range references to make sure they match your data.

    4. Variable declarations: Make sure that all your variables have been declared correctly. If you are trying to reference an undeclared variable or an object that has not been set, you will get a subscript out of range error. Double-check your variable names, data types, and scope to make sure they are correct.

    Once you have identified the source of the error, you can implement the appropriate solution to fix it. If you are still having trouble, please let me know which line in your code is causing the error, and I can help you further.

  5. User

    can the macro be adjusted to copy the last ten values but if row 4 is in the range found it will not copy row 4 to row 1

  6. Assistant

    Yes, we can modify the macro to skip copying the fourth row if it falls within the range of the last 10 rows.

    Here's the updated VBA code that should achieve this:

    Sub CopyLast10Values()
        'Calculate before copying
        Application.Calculate
        
        'Wait 4 seconds
        Application.Wait Now + TimeValue("0:00:04")
        
        'Copy last 10 values from column J of the sheet named in cell I2
        Dim srcSheet As Worksheet
        Dim dstSheet As Worksheet
        Set srcSheet = Sheets(Range("I2").Value)
        Set dstSheet = Sheets("Request Sheet")
        Dim lastRow As Long
        lastRow = srcSheet.Cells(srcSheet.Rows.Count, "J").End(xlUp).Row
        Dim startRow As Long
        startRow = IIf(lastRow - 9 < 4, 4, lastRow - 9)
        srcSheet.Range("J" & startRow & ":J" & lastRow).Copy
        
        'Paste copied values into column E starting from E5 of the Request Sheet
        Dim dstRange As Range
        If dstSheet.Range("E4") <> "" Then
            Set dstRange = dstSheet.Range("E5")
        Else
            Set dstRange = dstSheet.Range("E4")
        End If
        dstRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        
        'Skip copying the fourth row if it falls within the range of the last 10 rows
        Dim lastDstRow As Long
        lastDstRow = dstSheet.Cells(dstSheet.Rows.Count, "E").End(xlUp).Row
        If lastDstRow > 4 And lastDstRow - startRow < 9 Then
            dstSheet.Range("E4").ClearContents
        End If
    End Sub
    

    This version of the macro first calculates, waits 4 seconds, and then copies the last 10 values from column J of the sheet named in cell I2. It determines the starting row for the copy based on whether or not the fourth row falls within the range of the last 10 rows, and skips copying that row if necessary.

    Then the macro pastes the copied values into column E of the Request Sheet, starting from either E4 (if it is empty) or E5. Finally, if the fourth row falls within the range of the last 10 rows that were pasted, the macro clears the contents of cell E4.