Create new sheet on cell value and copy from another sheet

[manmah]

Hi All
I have a project where I want to create a new sheet based on the cell input. I have used the following VB:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsNew As Worksheet
If Target.Cells.Count > 1 Then Exit Sub

On Error Resume Next
If Not Intersect(Target, Range("N2:N1000")) Is Nothing Then
Set wsNew = Sheets(Target.Text)
If wsNew Is Nothing Then Sheets.Add().Name = Target.Text
End If



End Sub

This works great but i need it to now copy a form from another sheet and paste it into this newly created on. as follows:

Sub COPYREPORT()
'
' COPYREPORT Macro
'


'
Sheets("REPORT MASTER").Select
Cells.Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count) - this is the problem???
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-3
Range("G7:J7").Select
End Sub

The problem here is i don't know what the new sheet will be called.

Any help would be much appreciated.

Thanks
Mark

 

[JLGWhiz]

Hi All
I have a project where I want to create a new sheet based on the cell input. I have used the following VB:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsNew As Worksheet
If Target.Cells.Count > 1 Then Exit Sub

On Error Resume Next
If Not Intersect(Target, Range("N2:N1000")) Is Nothing Then
Set wsNew = Sheets(Target.Text)
If wsNew Is Nothing Then Sheets.Add().Name = Target.Text
End If

End Sub

This works great but i need it to now copy a form from another sheet and paste it into this newly created on. as follows:

Sub COPYREPORT()
'
' COPYREPORT Macro
'


'
Sheets("REPORT MASTER").Select
Cells.Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count) - this is the problem???
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-3
Range("G7:J7").Select
End Sub

The problem here is i don't know what the new sheet will be called.

Any help would be much appreciated.

Thanks
Mark

Try "ActiveSheet"

 

[manmah]

Try "ActiveSheet"

Ok i've tried that:

Sub COPYREPORT()
'
' COPYREPORT Macro
'
'
Sheets("REPORT MASTER").Select
Cells.Select
Selection.Copy
ActiveSheet.Select
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-3
Range("G7:J7").Select
End Sub

but I think its treating the REPORT MASTER sheet as the active sheet. so its just pasting it straight back on top of that one?

Thanks
Mark

 

[JLGWhiz]

Try it like this, and leave the Sheets.Add line in.

Sheets("REPORT MASTER").Select
Cells.Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count) - this is the problem???
ActiveSheet.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-3
Range("G7:J7").Select
End Sub