none
Generating a temporary worksheet with the first row copied from existing worksheet RRS feed

  • Question

  • Hello, I would like to know how I can create a temporary worksheet (for displaying temporary orders) that will delete after closing the userform with a Cancel button. How can I copy the first row of WorkSheet2 onto the temporary worksheet named "Temp", or have the first row displayed every time it is generated?

    Thank you!

    Saturday, October 13, 2018 4:26 AM

All replies

  • Try the following code. Copy all of the code into the Userform code module.

    Note that the Dim statement must be at the top of the Userform code module.

    The code tests for existence of the worksheet Temp and if does not exist then it creates it. If it already exists then it clears it.

    Row 1 of "WorkSheet2" is then copied to the worksheet Temp

    The code for the Cancel button delete the Temp worksheet. In the example, the name of the Cancel button is btnCancel.

    Copy all the following code to the Userform Module.

    Dim wsTemp As Worksheet     'At top of Userform code module


    Private Sub UserForm_Initialize()
       
        'Test if the worksheet Temp already exists
        On Error Resume Next
        Set wsTemp = Worksheets("Temp")
        On Error GoTo 0
       
        If wsTemp Is Nothing Then  'Id Nothing then does not already exist
            'Temp does not exist so add the sheet
            'Following line edit "WorkSheet2" to your worksheet name
            Set wsTemp = Sheets.Add(After:=Worksheets(Sheets.Count))
            wsTemp.Name = "Temp"    'Rename the added sheet
        Else
            'Worksheet Temp already exists so clear the worksheet
            wsTemp.Cells.ClearContents
        End If
       
        'Copy 1st row of WorkSheet2 to the added sheet Temp.
        Worksheets("WorkSheet2").Rows(1).Copy Destination:=wsTemp.Cells(1, 1)
     
    End Sub


    Private Sub btnCancel_Click()
        'this sub is called by button named btnCancel
        Application.DisplayAlerts = False
        wsTemp.Delete
        Application.DisplayAlerts = True
        Unload Me
    End Sub


    Regards, OssieMac

    Saturday, October 13, 2018 6:51 AM
  • Hi TheaJX,

    Did OssieMac's answer resolved your problem?

    You can mark it as answer if it helped and please help us close the thread.

    Best Regards,

    Lina



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Thursday, October 18, 2018 2:55 AM