Results 1 to 4 of 4

Thread: Userform

  1. #1
    Acolyte thousand's Avatar
    Join Date
    Apr 2017
    Posts
    25
    Articles
    0
    Excel Version
    2010

    Userform



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi

    I have a basic code which activates the userform but I am wanting to add a copy function in the form, is this possible by adjusting the existing code below
    Thanks in advance
    Private Sub Workbook_NewSheet(ByVal Sh As Object)
    If ActiveSheet.Name <> "Sheet1" Then
    ActiveSheet.ShowDataForm
    End If
    End Sub

  2. #2
    Conjurer Kenneth Hobson's Avatar
    Join Date
    Mar 2014
    Location
    Tecumseh, OK
    Posts
    130
    Articles
    0
    Excel Version
    365
    Code:
    Private Sub Workbook_NewSheet(ByVal Sh As Object)  
      Sheet1.UsedRange.Copy Sh.[A1]
      Application.CutCopyMode = False
      Sh.ShowDataForm
    End Sub

  3. #3
    Acolyte thousand's Avatar
    Join Date
    Apr 2017
    Posts
    25
    Articles
    0
    Excel Version
    2010
    Thanks Kenneth Hobson, not sure why but it didn't work...
    Userform appears but there is no copy button and no data gets copied

  4. #4
    Conjurer Kenneth Hobson's Avatar
    Join Date
    Mar 2014
    Location
    Tecumseh, OK
    Posts
    130
    Articles
    0
    Excel Version
    365
    DataForm<>Userform

    Of course the code goes into ThisWorkbook object.

    Sheet1 is the codename of the sheet which must have data to copy. If not data, it errors for new sheet. If data is not in a matrix in Sheet1 starting at A1, it will not copy data but does not show the dataform nor does it error.

    Obviously, you can use other data range in Sheet1 other than starting at A1. If you don't use UsedRange with data starting at A1, that has to be known.

    Attach an example file with data in Sheet1 if you like.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •