Userform

thousand

New member
Joined
Apr 2, 2017
Messages
30
Reaction score
0
Points
0
Excel Version(s)
2010
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
 
Code:
Private Sub Workbook_NewSheet(ByVal Sh As Object)  
  Sheet1.UsedRange.Copy Sh.[A1]
  Application.CutCopyMode = False
  Sh.ShowDataForm
End Sub
 
Thanks Kenneth Hobson, not sure why but it didn't work...
Userform appears but there is no copy button and no data gets copied
 
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.
 
Back
Top